pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedNovice SQL Question
3+ messages / 2 participants
[nested] [flat]
* Novice SQL Question
@ 2004-02-02 22:38 Terry Lee Tucker <terry@esc1.com>
2004-02-02 23:00 ` Re: Novice SQL Question Terry Lee Tucker <terry@esc1.com>
2004-02-03 03:14 ` Re: Novice SQL Question Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 3+ messages in thread
From: Terry Lee Tucker @ 2004-02-02 22:38 UTC (permalink / raw)
To: pgsql-sql
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-6812
Cell: 1-336-363-4719
email: terry@esc1.com
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Novice SQL Question
2004-02-02 22:38 Novice SQL Question Terry Lee Tucker <terry@esc1.com>
@ 2004-02-02 23:00 ` Terry Lee Tucker <terry@esc1.com>
1 sibling, 0 replies; 3+ messages in thread
From: Terry Lee Tucker @ 2004-02-02 23:00 UTC (permalink / raw)
To: pgsql-sql
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 than
in a petty officer, no more justifiable in a king than in a constable;
but is so much the worse in him, in that he has more trust put in him,
has already a much greater share than the rest of his brethren, and is
supposed from the advantages of his education, employment, and coun-
sellors, to be more knowing in the measures of right and wrong."
--John Locke
Work: 1-336-372-6812
Cell: 1-336-363-4719
email: terry@esc1.com
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Novice SQL Question
2004-02-02 22:38 Novice SQL Question Terry Lee Tucker <terry@esc1.com>
@ 2004-02-03 03:14 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 3+ messages in thread
From: Tom Lane @ 2004-02-03 03:14 UTC (permalink / raw)
To: Terry Lee Tucker <terry@esc1.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2004-02-03 03:14 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-02-02 22:38 Novice SQL Question Terry Lee Tucker <terry@esc1.com>
2004-02-02 23:00 ` Terry Lee Tucker <terry@esc1.com>
2004-02-03 03:14 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox