pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Stijn Vanroye <s.vanroye@farcourier.com>
To: pgsql-sql@postgresql.org
Cc: Viorel Dragomir <bc@vio.ro>
Subject: Re: select distinct and order by
Date: Wed, 28 Apr 2004 12:34:54 +0200
Message-ID: <71E201BE5E881C46811BA160694C5FCB046731@fs1000.farcourier.com> (raw)

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: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, 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



view thread (8+ messages)

Message-ID: <71E201BE5E881C46811BA160694C5FCB046731@fs1000.farcourier.com>
Permalink:  ../71E201BE5E881C46811BA160694C5FCB046731@fs1000.farcourier.com/
Also on:    postgresql.org/message-id/71E201BE5E881C46811BA160694C5FCB046731@fs1000.farcourier.com

 · 

reply

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 from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: s.vanroye@farcourier.com, bc@vio.ro
  Subject: Re: select distinct and order by
  In-Reply-To: <71E201BE5E881C46811BA160694C5FCB046731@fs1000.farcourier.com>
  References: <71E201BE5E881C46811BA160694C5FCB046731@fs1000.farcourier.com>

* 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