pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedSELECT DISTINCT and ORDER BY
8+ messages / 6 participants
[nested] [flat]
* SELECT DISTINCT and ORDER BY
@ 1999-09-15 08:53 Joerg Eriskat <Eriskat@icf.med.uni-muenchen.de>
1999-09-15 14:26 ` Re: [SQL] SELECT DISTINCT and ORDER BY Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 8+ messages in thread
From: Joerg Eriskat @ 1999-09-15 08:53 UTC (permalink / raw)
To: pgsql-sql
Hi,
I'm not sure it is a bug, so I'm asking the SQL gurus. I think
there is a inconsistency with SELECT DISTINCT queries when used in
combination with ORDER BY and column alias names (sorry for the rather
long example):
create table t1 (f1 int4);
insert into t1 values (1);
insert into t1 values (2);
insert into t1 values (2); -- double
create table t2 (f1 int4);
insert into t2 values (1);
insert into t2 values (2);
insert into t2 values (3);
-- this query seems ok:
select distinct a.f1 as x1, b.f1 as x2
into t3
from t1 a, t2 b;
-- this query does not select *distinct*:
select distinct a.f1 as x1, b.f1 as x2
into t4
from t1 a, t2 b
order by a.f1, b.f1;
-- but this one does:
select distinct a.f1 as x1, b.f1 as x2
into t5
from t1 a, t2 b
order by x1, x2;
select count(*) from t3;
select count(*) from t4;
select count(*) from t5;
[ output skipped ... ]
select count(*) from t3;
count
-----
6
(1 row)
select count(*) from t4;
count
-----
9
(1 row)
select count(*) from t5;
count
-----
6
(1 row)
This was with Postgres 6.5.1 and RedHat 6.0 on an i486. My apologies if
this is just my plain bad SQL or if it is a known issue (but I did not
find it in the history file for v. 6.5.2).
Greetings,
Joerg
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: [SQL] SELECT DISTINCT and ORDER BY
1999-09-15 08:53 SELECT DISTINCT and ORDER BY Joerg Eriskat <Eriskat@icf.med.uni-muenchen.de>
@ 1999-09-15 14:26 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 8+ messages in thread
From: Tom Lane @ 1999-09-15 14:26 UTC (permalink / raw)
To: Joerg Eriskat <Eriskat@icf.med.uni-muenchen.de>; +Cc: pgsql-sql
Joerg Eriskat <Eriskat@icf.med.uni-muenchen.de> writes:
> I'm not sure it is a bug, so I'm asking the SQL gurus. I think
> there is a inconsistency with SELECT DISTINCT queries when used in
> combination with ORDER BY and column alias names (sorry for the rather
> long example):
Looks like a bug to me. This seems to be fixed in the current sources,
probably as a result of the changes I made to the representation of sort
ordering. What is happening in 6.5.* is that the system is failing to
notice that the order-by values are the same as the ones being called
out as DISTINCT, so it generates additional columns and then sorts on
those instead of the DISTINCT ones. 6.6-to-be isn't fooled by the
aliases.
The underlying problem is still there, though: ordering by a column
not mentioned in the DISTINCT list doesn't do the right thing.
Arguably there is no right thing, and the code should raise an error,
but as of now it definitely isn't doing anything reasonable. It would
take two sort steps to handle this: sort by the DISTINCT columns,
filter out adjacent duplicates, then sort by the ORDER BY columns.
But we only do one sort, and we use the ORDER BY columns (plus any
DISTINCT columns not already in ORDER BY).
However, there's more to this than meets the eye. If you examine any
non-DISTINCT column after the filter step, then you are going to get a
randomly chosen one of the values associated with that set of DISTINCT
values. So a second sort step on such a column after the DISTINCT
sort/filter would not give well-defined results. That's why I think
maybe we should raise an error. (I don't like "DISTINCT ON column"
either, since it is *necessarily* ill-defined.)
regards, tom lane
^ permalink raw reply [nested|flat] 8+ messages in thread
* Select distinct and order by.
@ 2001-07-11 08:41 Carlos <carlos@solaria-mediterranea.com>
2001-07-17 13:25 ` Re: Select distinct and order by. Tod McQuillin <devin@spamcop.net>
2001-07-17 16:00 ` Re: Select distinct and order by. Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 8+ messages in thread
From: Carlos @ 2001-07-11 08:41 UTC (permalink / raw)
To: pgsql-sql
Hi all:
We have developed an aplication in C with postgresql, using libpq. Our
version of development is Postgresql 6.5.3 and it works fine.
Now we are migrating the application to various flavours of Linux
(S.u.s.e 7.1, tha uses postgresql 7.0.3; Debian Potato, that uses
postgresql 7.0.2) and in these dists the next query fails.
Select distinct field1, field2 from table1 order by field3;
The value return by PQresultErrorMessage is:
For SELECT DISTINCT, ORDER BY expressions must appear in target list
Whatever this query works fine in postgresql 6.5.3.
Is correct this query and so there was a bug on 6.5.3 or there is a bug
on the new versions?.
Also in certains situations (in versions 7.0.x) this query fails from
libpq:
Select distinct field1, field2, field3 from table1 order by field1,
field2;
but if we copy the statement with mouse and do:
$psql -d ourdb -c "Select distinct field1, field2, field3 from table1
order by field1, field2;" WORKS FINE.
These last situation are in a transaction whith some tables locked, that
aren't table1.
TIA
Carlos.
Solaria Mediterranea, S.L.L:
P.S.: please send me the answers directly, because I isn't subscribed to
the list.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Select distinct and order by.
2001-07-11 08:41 Select distinct and order by. Carlos <carlos@solaria-mediterranea.com>
@ 2001-07-17 13:25 ` Tod McQuillin <devin@spamcop.net>
1 sibling, 0 replies; 8+ messages in thread
From: Tod McQuillin @ 2001-07-17 13:25 UTC (permalink / raw)
To: Carlos <carlos@solaria-mediterranea.com>; +Cc: pgsql-sql
On Wed, 11 Jul 2001, Carlos wrote:
> Select distinct field1, field2 from table1 order by field3;
>
> The value return by PQresultErrorMessage is:
>
> For SELECT DISTINCT, ORDER BY expressions must appear in target list
That's because this query is ambiguous.
What if table1 looks like this:
field1 field2 field3
------ ------ ------
a b 1
a b 3
c d 2
What should your query return then? Both
a b
c d
and
c d
a b
are valid, depending on which 'a b' row was chosen.
If 6.5.3 allowed that, it was a mistake to rely on it because the answer
is undefined.
--
Tod McQuillin
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Select distinct and order by.
2001-07-11 08:41 Select distinct and order by. Carlos <carlos@solaria-mediterranea.com>
@ 2001-07-17 16:00 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 8+ messages in thread
From: Tom Lane @ 2001-07-17 16:00 UTC (permalink / raw)
To: Carlos <carlos@solaria-mediterranea.com>; +Cc: pgsql-sql
Carlos <carlos@solaria-mediterranea.com> writes:
> Select distinct field1, field2 from table1 order by field3;
> The value return by PQresultErrorMessage is:
> For SELECT DISTINCT, ORDER BY expressions must appear in target list
> Whatever this query works fine in postgresql 6.5.3.
> Is correct this query and so there was a bug on 6.5.3 or there is a bug
> on the new versions?.
6.5 was in error to accept that query. The problem with it is: which
value of field3 should be used to sort, if multiple rows with the same
field1/field2 are being collapsed together? The results aren't
well-defined.
You can probably accomplish what you want in a slightly better-defined
way with SELECT DISTINCT ON. See the SELECT reference page.
> Also in certains situations (in versions 7.0.x) this query fails from
> libpq:
"Fails" how?
regards, tom lane
^ permalink raw reply [nested|flat] 8+ messages in thread
* select distinct and order by
@ 2004-04-28 09:27 Stijn Vanroye <s.vanroye@farcourier.com>
2004-04-28 09:54 ` Re: select distinct and order by Viorel Dragomir <bc@vio.ro>
0 siblings, 1 reply; 8+ messages in thread
From: Stijn Vanroye @ 2004-04-28 09:27 UTC (permalink / raw)
To: pgsql-sql
Hello everybody,
I have a short question:
I'm trying to run the following query:
select distinct on (staff_id) staff_id, fullname, loginname from staff
where staff_id in
(select staff_id from staffmembership where staffgroup_id=#SomeIdValue#)
order by fullname
I get the follwing error:
select distinct on expressions must match the initial order by expression
Does this mean that I can only order by the same fields as the ones that I use in the distinct?
If so, is there still a way that I can select distinct on the keyfield, and still sort by the name. However unlikely, it could happen that two people have the same name, so a distinct on fullname could make problems.
The facts:
- PostgreSQL 7.3.2 running on RH 9
- pgODBC 7.3.0200
- Borland Delphi 7 enterprise
Regards,
Stijn Vanroye
-=[Today I got more responsabilities from my boss, as from now I'm responsible for everything that goes wrong ...]=-
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: select distinct and order by
2004-04-28 09:27 select distinct and order by Stijn Vanroye <s.vanroye@farcourier.com>
@ 2004-04-28 09:54 ` Viorel Dragomir <bc@vio.ro>
0 siblings, 0 replies; 8+ messages in thread
From: Viorel Dragomir @ 2004-04-28 09:54 UTC (permalink / raw)
To: Stijn Vanroye <s.vanroye@farcourier.com>; pgsql-sql
I don't know if this will work, but why not try it ? :)
select distinct fullname, distinct staff_id, loginname from staff
where staff_id in
(select staff_id from staffmembership where staffgroup_id=#SomeIdValue#)
order by fullname, staff_id
Next one is more complicate but with more chances to succeed :)
select staff_id, fullname, loginname from (
select distinct on (staff_id) staff_id, fullname, loginname from staff
where staff_id in
(select staff_id from staffmembership where staffgroup_id=#SomeIdValue#)
order by staff_id
)
order by fullname
Let me know
:)
----- Original Message -----
From: Stijn Vanroye
To: pgsql-sql@postgresql.org
Sent: Wednesday, April 28, 2004 12:27
Subject: [SQL] select distinct and order by
Hello everybody,
I have a short question:
I'm trying to run the following query:
select distinct on (staff_id) staff_id, fullname, loginname from staff
where staff_id in
(select staff_id from staffmembership where staffgroup_id=#SomeIdValue#)
order by fullname
I get the follwing error:
select distinct on expressions must match the initial order by expression
Does this mean that I can only order by the same fields as the ones that I use in the distinct?
If so, is there still a way that I can select distinct on the keyfield, and still sort by the name. However unlikely, it could happen that two people have the same name, so a distinct on fullname could make problems.
The facts:
- PostgreSQL 7.3.2 running on RH 9
- pgODBC 7.3.0200
- Borland Delphi 7 enterprise
Regards,
Stijn Vanroye
-=[Today I got more responsabilities from my boss, as from now I'm responsible for everything that goes wrong ...]=-
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: select distinct and order by
@ 2004-04-28 10:34 Stijn Vanroye <s.vanroye@farcourier.com>
0 siblings, 0 replies; 8+ messages in thread
From: Stijn Vanroye @ 2004-04-28 10:34 UTC (permalink / raw)
To: pgsql-sql; +Cc: Viorel Dragomir <bc@vio.ro>
I solved it. Your first answer gave me a slap on the head (and by god I needed one :-) ). I simply used this:
select distinct staff_id, fullname, loginname from staff
where staff_id in
(select staff_id from staffmembership where staffgroup_id=#someid#)
order by fullname
By omitting the on(staff_id) I believe it now distincts on all the fields. At first I was thinking that this would cause a problem, but it is not. Even if the fullname and the loginname are different the id will still seperate them. And a situation where the id is the same and the fullname and loginname are different is impossible.
In your first option you use the word 'distinct' again, but this wil most certainly raise a parse error.
The second option will work. It's not that complicated but two subselects for 3 fields from one and the same table may be a bit much though :-)
Thanks for the help,
Stijn Vanroye
-----Original Message-----
From: Viorel Dragomir [mailto:bc@vio.ro]
Sent: woensdag 28 april 2004 11:54
To: Stijn Vanroye; pgsql-sql@postgresql.org
Subject: Re: [SQL] select distinct and order by
I don't know if this will work, but why not try it ? :)
select distinct fullname, distinct staff_id, loginname from staff
where staff_id in
(select staff_id from staffmembership where staffgroup_id=#SomeIdValue#)
order by fullname, staff_id
Next one is more complicate but with more chances to succeed :)
select staff_id, fullname, loginname from (
select distinct on (staff_id) staff_id, fullname, loginname from staff
where staff_id in
(select staff_id from staffmembership where staffgroup_id=#SomeIdValue#)
order by staff_id
)
order by fullname
Let me know
:)
----- Original Message -----
From: Stijn Vanroye
To: pgsql-sql@postgresql.org
Sent: Wednesday, April 28, 2004 12:27
Subject: [SQL] select distinct and order by
Hello everybody,
I have a short question:
I'm trying to run the following query:
select distinct on (staff_id) staff_id, fullname, loginname from staff
where staff_id in
(select staff_id from staffmembership where staffgroup_id=#SomeIdValue#)
order by fullname
I get the follwing error:
select distinct on expressions must match the initial order by expression
Does this mean that I can only order by the same fields as the ones that I use in the distinct?
If so, is there still a way that I can select distinct on the keyfield, and still sort by the name. However unlikely, it could happen that two people have the same name, so a distinct on fullname could make problems.
The facts:
- PostgreSQL 7.3.2 running on RH 9
- pgODBC 7.3.0200
- Borland Delphi 7 enterprise
Regards,
Stijn Vanroye
-=[Today I got more responsabilities from my boss, as from now I'm responsible for everything that goes wrong ...]=-
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2004-04-28 10:34 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-09-15 08:53 SELECT DISTINCT and ORDER BY Joerg Eriskat <Eriskat@icf.med.uni-muenchen.de>
1999-09-15 14:26 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-07-11 08:41 Select distinct and order by. Carlos <carlos@solaria-mediterranea.com>
2001-07-17 13:25 ` Re: Select distinct and order by. Tod McQuillin <devin@spamcop.net>
2001-07-17 16:00 ` Re: Select distinct and order by. Tom Lane <tgl@sss.pgh.pa.us>
2004-04-28 09:27 select distinct and order by Stijn Vanroye <s.vanroye@farcourier.com>
2004-04-28 09:54 ` Re: select distinct and order by Viorel Dragomir <bc@vio.ro>
2004-04-28 10:34 Re: select distinct and order by Stijn Vanroye <s.vanroye@farcourier.com>
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