On 22 Sep 2001 19:18:10 -0700, [EMAIL PROTECTED] (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. ---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
