Simple Query HELP!!!

[Paolo Colonnello]

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,

[Bob Barrows]

On 22 Sep 2001 19:18:10 -0700, bu...@colonnello.org (Paolo Colonnello)
wrote:

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.

[Paolo Colonnello]

reb_...@yahoo.com (Bob Barrows) wrote in message news:<3bad5086...@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

[--CELKO--]

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?

CREATE TABLE People
(name CHAR(30) NOT NULL PRIMARY KEY, -- not big enough for TEXT
age INTEGER NOT NULL, -- should be birthdate instead
company CHAR(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 P1
WHERE NOT EXISTS
(SELECT *
FROM People AS P2
WHERE P1.company = P2.company
AND P1.age < P2.age);

This says there is nobody older than the P1 person in the same
company.

[Paolo Colonnello]

71062...@compuserve.com (--CELKO--) wrote in message news:<c0d87ec0.01092...@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....