MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Subject: select distinct and order by
content-class: urn:content-classes:message
Date: Wed, 28 Apr 2004 11:27:06 +0200
Message-ID: <71E201BE5E881C46811BA160694C5FCB046730@fs1000.farcourier.com>
Thread-Topic: [SQL] select distinct and order by
Thread-Index: AcQtAvke5FKMtco5SaKHVI/FUsTzXg==
From: "Stijn Vanroye" <s.vanroye@farcourier.com>
To: <pgsql-sql@postgresql.org>

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=20
  (select staff_id from staffmembership where staffgroup_id=3D#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

-=3D[Today I got more responsabilities from my boss, as from now I'm respon=
sible for everything that goes wrong ...]=3D-