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:[EMAIL PROTECTED] Sent: woensdag 28 april 2004 11:54 To: Stijn Vanroye; [EMAIL PROTECTED] 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: [EMAIL PROTECTED] 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 ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
