From: Tom Lane <tgl@sss.pgh.pa.us>
To: Terry Lee Tucker <terry@esc1.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Novice SQL Question
Date: Mon, 02 Feb 2004 22:14:02 -0500
Message-ID: <10684.1075778042@sss.pgh.pa.us> (raw)
In-Reply-To: <200402021738.06828.terry@esc1.com>
References: <200402021738.06828.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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, terry@esc1.com
Subject: Re: Novice SQL Question
In-Reply-To: <10684.1075778042@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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