pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Simple 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