X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.2])
	by svr1.postgresql.org (Postfix) with ESMTP id 323BDD1B8B1
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 28 Apr 2004 06:48:43 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024)
	with ESMTP id 73138-09
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 28 Apr 2004 06:48:43 -0300 (ADT)
Received: from mail1.dr.myx.net (mail1.dr.myx.net [217.10.193.161])
	by svr1.postgresql.org (Postfix) with ESMTP id 90A19D1B893
	for <pgsql-sql@postgresql.org>; Wed, 28 Apr 2004 06:48:39 -0300 (ADT)
Received: from appserver (unknown [82.137.2.202])
	by mail1.dr.myx.net (mydomain.myx.net) with ESMTP
	id 880543009C8; Wed, 28 Apr 2004 12:48:39 +0300 (EEST)
Message-ID: <01f201c42d06$c34020b0$6500a8c0@wylog.local>
From: "Viorel Dragomir" <bc@vio.ro>
To: "Stijn Vanroye" <s.vanroye@farcourier.com>,
	<pgsql-sql@postgresql.org>
References: <71E201BE5E881C46811BA160694C5FCB046730@fs1000.farcourier.com>
Subject: Re: select distinct and order by
Date: Wed, 28 Apr 2004 12:54:13 +0300
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_NextPart_000_01EF_01C42D1F.E81605D0"
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook Express 6.00.3790.0
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.132
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Spam-Status: No, hits=0.5 tagged_above=0.0 required=5.0 tests=HTML_20_30,
	HTML_MESSAGE
X-Spam-Level: 
X-Archive-Number: 200404/312
X-Sequence-Number: 17687

This is a multi-part message in MIME format.

------=_NextPart_000_01EF_01C42D1F.E81605D0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

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

Let me know
:)
  ----- Original Message -----=20
  From: Stijn Vanroye=20
  To: pgsql-sql@postgresql.org=20
  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=20
    (select staff_id from staffmembership where staffgroup_id=3D#SomeIdValu=
e#)
  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, a=
nd still sort by the name. However unlikely, it could happen that two peopl=
e 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 resp=
onsible for everything that goes wrong ...]=3D-

  ---------------------------(end of broadcast)---------------------------
  TIP 7: don't forget to increase your free space map settings
------=_NextPart_000_01EF_01C42D1F.E81605D0
Content-Type: text/html;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; charset=3Diso-8859-1">
<META content=3D"MSHTML 6.00.3790.118" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV><FONT face=3DArial size=3D2>I don't know if this will work, but why no=
t try it=20
? :)</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV>select distinct fullname, distinct staff_id, loginname from staff<BR>w=
here=20
staff_id in <BR>&nbsp; (select staff_id from staffmembership where=20
staffgroup_id=3D#SomeIdValue#)<BR>order by fullname, staff_id</DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>Next one is more complicate but with more =
chances=20
to succeed :)</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>select staff_id, fullname, loginname from=
=20
(</FONT></DIV>
<DIV>select distinct on (staff_id) staff_id, fullname, loginname from=20
staff<BR>where staff_id in <BR>&nbsp; (select staff_id from staffmembership=
=20
where staffgroup_id=3D#SomeIdValue#)<BR>order by staff_id</DIV>
<DIV>)</DIV>
<DIV>order by fullname</DIV>
<DIV>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>Let me know</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>:)</FONT></DIV>
<BLOCKQUOTE=20
style=3D"PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LE=
FT: #000000 2px solid; MARGIN-RIGHT: 0px">
  <DIV style=3D"FONT: 10pt arial">----- Original Message ----- </DIV>
  <DIV=20
  style=3D"BACKGROUND: #e4e4e4; FONT: 10pt arial; font-color: black"><B>Fro=
m:</B>=20
  <A title=3Ds.vanroye@farcourier.com href=3D"mailto:s.vanroye@farcourier.c=
om">Stijn=20
  Vanroye</A> </DIV>
  <DIV style=3D"FONT: 10pt arial"><B>To:</B> <A title=3Dpgsql-sql@postgresq=
l.org=20
  href=3D"mailto:pgsql-sql@postgresql.org">pgsql-sql@postgresql.org</A> </D=
IV>
  <DIV style=3D"FONT: 10pt arial"><B>Sent:</B> Wednesday, April 28, 2004=20
  12:27</DIV>
  <DIV style=3D"FONT: 10pt arial"><B>Subject:</B> [SQL] select distinct and=
 order=20
  by</DIV>
  <DIV><BR></DIV>Hello everybody,<BR><BR>I have a short question:<BR><BR>I'=
m=20
  trying to run the following query:<BR>select distinct on (staff_id) staff=
_id,=20
  fullname, loginname from staff<BR>where staff_id in <BR>&nbsp; (select=20
  staff_id from staffmembership where staffgroup_id=3D#SomeIdValue#)<BR>ord=
er by=20
  fullname<BR><BR>I get the follwing error:<BR>select distinct on expressio=
ns=20
  must match the initial order by expression<BR><BR>Does this mean that I c=
an=20
  only order by the same fields as the ones that I use in the distinct?<BR>=
If=20
  so, is there still a way that I can select distinct on the keyfield, and =
still=20
  sort by the name. However unlikely, it could happen that two people have =
the=20
  same name, so a distinct on fullname could make problems.<BR><BR>The=20
  facts:<BR>- PostgreSQL 7.3.2 running on RH 9<BR>- pgODBC 7.3.0200<BR>- Bo=
rland=20
  Delphi 7 enterprise<BR><BR>Regards,<BR><BR>Stijn Vanroye<BR><BR>-=3D[Toda=
y I got=20
  more responsabilities from my boss, as from now I'm responsible for every=
thing=20
  that goes wrong ...]=3D-<BR><BR>---------------------------(end of=20
  broadcast)---------------------------<BR>TIP 7: don't forget to increase =
your=20
  free space map settings</BLOCKQUOTE></BODY></HTML>

------=_NextPart_000_01EF_01C42D1F.E81605D0--