Skip site navigation (1) Skip section navigation (2)

select distinct and order by

From: "Stijn Vanroye" <s(dot)vanroye(at)farcourier(dot)com>
To: <pgsql-sql(at)postgresql(dot)org>
Subject: select distinct and order by
Date: 2004-04-28 09:27:06
Message-ID: 71E201BE5E881C46811BA160694C5FCB046730@fs1000.farcourier.com (view raw or flat)
Thread:
Lists: 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 ...]=-

Responses

pgsql-sql by date

Next:From: Viorel DragomirDate: 2004-04-28 09:54:13
Subject: Re: select distinct and order by
Previous:From: Bruno Wolff IIIDate: 2004-04-28 06:48:13
Subject: Re: date arithmetic over calender year boundaries

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group