From: Viorel Dragomir <bc@vio.ro>
To: Stijn Vanroye <s.vanroye@farcourier.com>
To: pgsql-sql@postgresql.org
Subject: Re: select distinct and order by
Date: Wed, 28 Apr 2004 12:54:13 +0300
Message-ID: <01f201c42d06$c34020b0$6500a8c0@wylog.local> (raw)
References: <71E201BE5E881C46811BA160694C5FCB046730@fs1000.farcourier.com>
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
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: bc@vio.ro, s.vanroye@farcourier.com
Subject: Re: select distinct and order by
In-Reply-To: <01f201c42d06$c34020b0$6500a8c0@wylog.local>
* 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