pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedSimple Query HELP!!!
5+ messages / 3 participants
[nested] [flat]
* Simple Query HELP!!!
@ 2001-09-23 02:18 Paolo Colonnello <bulk@colonnello.org>
2001-09-23 03:12 ` Re: Simple Query HELP!!! Bob Barrows <reb_01501@yahoo.com>
2001-09-24 03:49 ` Re: Simple Query HELP!!! --CELKO-- <71062.1056@compuserve.com>
0 siblings, 2 replies; 5+ messages in thread
From: bulk@colonnello.org @ 2001-09-23 02:18 UTC (permalink / raw)
To: pgsql-sql
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,
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Simple Query HELP!!!
2001-09-23 02:18 Simple Query HELP!!! Paolo Colonnello <bulk@colonnello.org>
@ 2001-09-23 03:12 ` Bob Barrows <reb_01501@yahoo.com>
2001-09-23 17:49 ` Re: Simple Query HELP!!! Paolo Colonnello <bulk@colonnello.org>
1 sibling, 1 reply; 5+ messages in thread
From: reb_01501@yahoo.com @ 2001-09-23 03:12 UTC (permalink / raw)
To: pgsql-sql
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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Simple Query HELP!!!
2001-09-23 02:18 Simple Query HELP!!! Paolo Colonnello <bulk@colonnello.org>
2001-09-23 03:12 ` Re: Simple Query HELP!!! Bob Barrows <reb_01501@yahoo.com>
@ 2001-09-23 17:49 ` Paolo Colonnello <bulk@colonnello.org>
0 siblings, 0 replies; 5+ messages in thread
From: bulk@colonnello.org @ 2001-09-23 17:49 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Simple Query HELP!!!
2001-09-23 02:18 Simple Query HELP!!! Paolo Colonnello <bulk@colonnello.org>
@ 2001-09-24 03:49 ` --CELKO-- <71062.1056@compuserve.com>
2001-10-04 18:16 ` Re: Simple Query HELP!!! Paolo Colonnello <bulk@colonnello.org>
1 sibling, 1 reply; 5+ messages in thread
From: 71062.1056@compuserve.com @ 2001-09-24 03:49 UTC (permalink / raw)
To: pgsql-sql
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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Simple Query HELP!!!
2001-09-23 02:18 Simple Query HELP!!! Paolo Colonnello <bulk@colonnello.org>
2001-09-24 03:49 ` Re: Simple Query HELP!!! --CELKO-- <71062.1056@compuserve.com>
@ 2001-10-04 18:16 ` Paolo Colonnello <bulk@colonnello.org>
0 siblings, 0 replies; 5+ messages in thread
From: bulk@colonnello.org @ 2001-10-04 18:16 UTC (permalink / raw)
To: pgsql-sql
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....
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2001-10-04 18:16 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-09-23 02:18 Simple Query HELP!!! Paolo Colonnello <bulk@colonnello.org>
2001-09-23 03:12 ` Bob Barrows <reb_01501@yahoo.com>
2001-09-23 17:49 ` Paolo Colonnello <bulk@colonnello.org>
2001-09-24 03:49 ` --CELKO-- <71062.1056@compuserve.com>
2001-10-04 18:16 ` Paolo Colonnello <bulk@colonnello.org>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox