Thread: select distinct and order by

select distinct and order by

From

"Stijn Vanroye"

Date:

28 April 2004, 09:27:26

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,
itcould 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 ...]=-

Re: select distinct and order by

From

"Viorel Dragomir"

Date:

28 April 2004, 09:48:45

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

Re: select distinct and order by

From

"Stijn Vanroye"

Date:

28 April 2004, 10:35:22

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
causea problem, but it is not. Even if the fullname and the loginname are different the id will still seperate them.
Anda 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
bea 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,
itcould 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