Thread: Simple Query HELP!!!
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? Please help,
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.
reb_01501@yahoo.com (Bob Barrows) wrote in message news:<3bad5086.29922681@news.charter.net>... > 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 > Thanks a lot, the query work perfect! I dont care about ties... this is just the query I need... Bye
Please write DDL and not narrative. here is my guess at what you are trying to do. What you posted was not a table because you had no key.TEXT is not the datatype to use for names -- unlessthey are thousand of characters long!! Recording age as an integer is useless -- give us the birthday and we can always compute their age. Is this what you meant to post? CREATE TABLE People (name CHAR(30) NOT NULL PRIMARY KEY, -- not big enough for TEXTage INTEGER NOT NULL, -- should be birthdate insteadcompanyCHAR(30) NOT NULL); >> ... create a query than get me a list with the seniors per company, for example :<< SELECT P1.name, P1.age, P1.company FROM People AS P1WHERE NOT EXISTS (SELECT * FROM People AS P2 WHEREP1.company = P2.company AND P1.age < P2.age); This says there is nobody older than the P1 person in the same company.
71062.1056@compuserve.com (--CELKO--) wrote in message news:<c0d87ec0.0109231949.4080c0a1@posting.google.com>... > Please write DDL and not narrative. here is my guess at what you are > trying to do. What you posted was not a table because you had no key. > TEXT is not the datatype to use for names -- unless they are thousand > of characters long!! > Recording age as an integer is useless -- give us the birthday and we > can always compute their age. Is this what you meant to post? > I didnt give DDL because was to abstract to explain, this is just an example, i did translate the query (really was a subquery in a IN ) and it work... dont care about the data types, I was interest only in the relations....