Обсуждение: Novice SQL Question
I need to the following query: select distinct event_code, level from logs join stat on (stat.prime is not null) where order_num = 130680; Ok, no problem. Does exactly what I want; however, I need to sort this is a particular way to get the right results. When I try to add the order by clause, I get an error. Here is the ORDER BY: ORDER BY event_date DESC, event_time DESC, event_secs DESC 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 No matter where I put it I get errors. Obviously, I'm a novice. Can somebody give me any advice? I'm using Postgres 7.2.3 on RedHat. Thanks... -- Work: 1-336-372-6812Cell: 1-336-363-4719 email: terry@esc1.com
To answer my own question: I discoverd that the order by fields had to be in the select list. Apparently, this is a requirement when using "DISTINCT". On Monday 02 February 2004 05:38 pm, Terry Lee Tucker wrote: > I need to the following query: > select distinct event_code, level from logs join stat on (stat.prime is not > null) where order_num = 130680; > > Ok, no problem. Does exactly what I want; however, I need to sort this is a > particular way to get the right results. When I try to add the order by > clause, I get an error. Here is the ORDER BY: > ORDER BY event_date DESC, event_time DESC, event_secs DESC > > 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 > > No matter where I put it I get errors. Obviously, I'm a novice. Can > somebody give me any advice? I'm using Postgres 7.2.3 on RedHat. > > Thanks... -- Quote: 48 "Exceeding the bounds of authority is no more a right in a great thanin a petty officer, no more justifiable in a king thanin a constable;but is so much the worse in him, in that he has more trust put in him,has already a much greater sharethan the rest of his brethren, and issupposed from the advantages of his education, employment, and coun-sellors, tobe more knowing in the measures of right and wrong." --John Locke Work: 1-336-372-6812Cell: 1-336-363-4719 email: terry@esc1.com
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