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:

> For this query ..
>
> SELECT DISTINCT t0_s.rec_num
> ...
> ORDER BY t0_s.date_of_request DESC

Well, what should the database do if two different records with the same
"rec_num" have different "date_of_request"s? You may know that won't happen
but the database doesn't know that.

Postgres doesn't really care what columns you're looking at, just that
date_of_request be part of the distinct criteria which for "distinct" means
being in the select list. If you use the equivalent GROUP BY instead you could
leave it off the select list but you would still have to have it in the GROUP
BY clause.

Note though that in 7.3 the distinct can be more efficient than the group by
version. In 7.4 the group by might be faster.

--
greg