471,092 Members | 1,547 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,092 software developers and data experts.

Query help -- extract data, thanks!

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

Jul 23 '05 #1
7 1325
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....!!!!

Jul 23 '05 #2
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)

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

Jul 23 '05 #4
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....!!!!


Jul 23 '05 #5
No worries about the re-formatting! I got the same problem... I will
delete them before running the query. Thanks.

Jul 23 '05 #6
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)
Jul 23 '05 #7
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!

Jul 23 '05 #8

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by Joe Gazda | last post: by
9 posts views Thread by netpurpose | last post: by
6 posts views Thread by Martin Lacoste | last post: by
2 posts views Thread by EricD | last post: by
3 posts views Thread by Serious_Practitioner | last post: by
5 posts views Thread by Samik2003 | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.