Thread: Why must SELECT DISTINCT, ORDER BY expressions must appear in target list?

For this query ..

SELECT DISTINCT t0_s.rec_num FROM sample_request t0_s,
 sample_request_line_item t1_li WHERE (t1_li.fulfillment_status =
'Shipped'
AND t1_li.followup_date <= '2003-02-04 20:00:00.000000000+00' AND
(t1_li.customer_prospect_feedback IS NULL OR
t1_li.customer_prospect_feedback = '')) AND
(t0_s.rec_num=t1_li.parentRequest) ORDER BY t0_s.date_of_request DESC


I get this error.

ERROR:  For SELECT DISTINCT, ORDER BY expressions must appear in target
list


This seems like a bug. Why do I need date_of_request in the select
section? SQL Spec?

Dave Smith <dave.smith@candata.com> writes:
> ERROR:  For SELECT DISTINCT, ORDER BY expressions must appear in target
> list

> This seems like a bug.

No, it isn't.  Consider

    SELECT DISTINCT x FROM tab ORDER BY y;

Assuming there are multiple values of y for any given x, how would you
expect the result to be sorted?  It's ill-defined.

            regards, tom lane