Re: Novice SQL Question

Terry Lee Tucker <terry@esc1.com> writes:
> If I put this where I thought it should go as in:
> select distinct event_code,level from logs join stat on (stat.prime is not 
> null) where order_num = 130680 order by event_date,event_time,event_secs;

> I get the following error:
> ERROR:  For SELECT DISTINCT, ORDER BY expressions must appear in target list

Right.  Think about what SELECT DISTINCT does: it combines all rows with
the same values of event_code and level into a single row.  The group of
rows with a particular pair of event_code/level might contain many
different values of event_date etc.  Which of these values should be
used to sort the combined row?  The result just isn't well-defined in
general.  You need to alter the query so that it completely defines
the result you want.  One way to do that is suggested by the error
message: add the ORDER BY columns into the DISTINCT list.  But that's
not necessarily the way that will get the result you want.
        regards, tom lane