Query help -- extract data, thanks!

[rong.guo]

Hello,

Please see the original data below. I would like to extract the account
with the earliest opening date only, and leave all the accounts opened
after that out. Could anybody help me with the query? Thanks a lot!

Original Data
person_id account open_date
10001 1111111 5/15/2003
10001 2222222 6/20/2004
10001 3333333 2/16/2005

Ideal Output
person_id account open_date
10001 1111111 5/15/2003

[Query Builder]

Select *

From OriginalData O

Where Open_Date = (Select MIN(Open_Date) From OriginalData Where
Person_ID = O.Person_ID)

Let me know if it errors out....!!!!

[Query Builder]

Sorry guys... These crazy '>' kills the script....!!! Here is the clean
version again...!

Select *

From OriginalData O

Where Open_Date = (Select MIN(Open_Date) From OriginalData Where
Person_ID = O.Person_ID)

[Query Builder]

Not sure why it keeps messing my text.... Google needs to fix
this.....!!!!!!! tough luck.. I dont want to even try formating it
again....

[rong.guo]

Thanks! It works!

However, when I tried another set of raw data which has two person_id
(please see below), I only got one record in output, while I am
expecting two. Any clue? Thanks!

Select *

From test Where zip = (Select MIN(zip) From test Where
Person_ID = test.Person_ID)

Raw Data
person_id zip state
10001 11111 AA
10001 22222 BB
10002 11113 AA

Output
person_id zip state
10001 11111 AA

Ideal Output
person_id zip state
10001 11111 AA
10002 11113 AA

Query Builder wrote: Select *

From OriginalData O

Where Open_Date = (Select MIN(Open_Date) From OriginalData Where
Person_ID = O.Person_ID)

Let me know if it errors out....!!!!

[rong.guo]

No worries about the re-formatting! I got the same problem... I will
delete them before running the query. Thanks.

[Hugo Kornelis]

On 16 Mar 2005 11:45:29 -0800, ro******@gmail.com wrote:

Thanks! It works!

However, when I tried another set of raw data which has two person_id
(please see below), I only got one record in output, while I am
expecting two. Any clue? Thanks!

(snip)

Hi rong.guo,

Your query is for the person (or persons) with the lowest ZIP code. The
output you expect shows two ZIP codes: 11111 and 11113. Is this because
you actually wanted the 2 lowest ZIP codes, or because you actually
wanted the lowest state?

For lowest state try

SELECT person_id, zip, state
FROM test AS t1
WHERE state = (SELECT MIN(state)
FROM test AS t2
WHERE t2.Person_ID = t1.Person_ID)

For the 2 lowest ZIP codes, try

SELECT TOP 2 person_id, zip, state
FROM test
ORDER BY zip

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[rong.guo]

Thanks Hugo!

What I wanted is the lowest zip code for EACH PERSON, together with the
state of the lowest zip code. Because some people do have multiple zip
codes and states, the idea is to only keep the lowest zip, and get rid
of others. Your query works well, I only changed min(state) to
min(zip).

Thanks again!