Received: from news.tht.net (news.hub.org [216.126.91.242]) by postgresql.org (8.11.3/8.11.4) with ESMTP id f8N33pe07692 for ; Sat, 22 Sep 2001 23:03:51 -0400 (EDT) (envelope-from news@news.tht.net) Received: (from news@localhost) by news.tht.net (8.11.4/8.11.4) id f8N2srf67778 for pgsql-sql@postgresql.org; Sat, 22 Sep 2001 22:54:53 -0400 (EDT) (envelope-from news) From: reb_01501@yahoo.com (Bob Barrows) X-Newsgroups: cz.comp.microsoft.sql, mailing.database.sql-general, comp.databases.ms-sqlserver, comp.databases.postgresql.sql Subject: Re: Simple Query HELP!!! Date: Sun, 23 Sep 2001 03:12:12 GMT Organization: Posted via Supernews, http://www.supernews.com Message-ID: <3bad5086.29922681@news.charter.net> References: X-Newsreader: Forte Free Agent 1.21/32.243 X-Complaints-To: newsabuse@supernews.com Lines: 38 To: pgsql-sql@postgresql.org X-Archive-Number: 200109/244 X-Sequence-Number: 4926 On 22 Sep 2001 19:18:10 -0700, bulk@colonnello.org (Paolo Colonnello) wrote: >Hello, I have the following, A table call People with 3 fields AGE >(Int) NAME (Txt) COMPANY (TxT) and I want to create a query than get >me a list with the seniors per company, for example : > >table PEOPLE > >NAME AGE COMPANY >Bob 33 Acme >Jane 30 Acme >Bill 20 Acme >Jose 56 ATech >Siu 40 ATech >Paolo 28 IBM >Maria 38 IBM > >I need a query than will return a list with the seniors per company >like > >Bob >Jose >Maria > >Is there a way to do this with one query? > Do you care about ties? What if Ingrid, 38 yrs old, worked at IBM? Would you want to show both Ingrid and Maria? If so, this will work: Select Name From People t1 Inner Join (Select Company, Max(Age) As Oldest FROM People Group By Company) t2 ON t1.Company = t2.Company AND t1.Age = t2.Oldest HTH, Bob Barrows Please reply to the newsgroup. My reply-to address is my "spam trap" and I don't check it very often.