471,108 Members | 1,278 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

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

Exatract Data, thanks.

Greetings!

I would like to extract data from the raw data below, here are the
criteria:

(1) I want accounts starting with 2 only, i.e., excluding accounts
starting with 3.
(2) I want to keep the person_id, account, and open_date for each
account starting with 2.
(3) I want to create a new variable to show each person's earliest
open_date of the accounts starting with 2.

My query below would give me person 10001's earliest open_date for
EVERYONE. Can anyone tell me what the problem is? Thanks a lot!

select person_id, account, open_date
,min_open=(select min(open_date) from test_5 where
person_id=test_5.person_id
and account like '2%')
from test_5
where account like '2%'

-Raw Data-
person_id account open_date
10001 22000001 5/15/2003
10001 22000002 6/20/2004
10001 30000001 2/2/2002
10002 22000003 8/12/2004
10002 22000004 9/15/2004
10002 30000002 2/16/2005

-Ideal Output-
person_id account open_date Min_Open
10001 22000001 5/15/2003 5/15/2003
10001 22000002 6/20/2004 5/15/2003
10002 22000003 8/12/2004 8/12/2004
10002 22000004 9/15/2004 8/12/2004

-My Output-
person_id account open_date Min_Open
10001 22000001 5/15/2003 5/15/2003
10001 22000002 6/20/2004 5/15/2003
10002 22000003 8/12/2004 5/15/2003
10002 22000004 9/15/2004 5/15/2003

Jul 23 '05 #1
3 1073
On 16 Mar 2005 12:17:29 -0800, ro******@gmail.com wrote:

(snip)
My query below would give me person 10001's earliest open_date for
EVERYONE. Can anyone tell me what the problem is? Thanks a lot!

select person_id, account, open_date
,min_open=(select min(open_date) from test_5 where
person_id=test_5.person_id
and account like '2%')
from test_5
where account like '2%'


Hi rong.guo,

You didn't post CREATE TABLE and INSERT statements, so I can't test it,
but I guess that this is caused because you forgot to use aliasses to
distinguish the outer and the inner version of the table.

select person_id, account, open_date
,min_open=(select min(open_date)
from test_5 AS t2
where t2.person_id = t1.person_id
and account like '2%')
from test_5 AS t1
where account like '2%'

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)
Jul 23 '05 #2
Thanks Hugo! It works perfectly!

I also tried Method 2, which gave me the same result as your method. I
am more used to using JOIN function. But I am wondering if Method 1 has
any advantages over Method 2 in the long term. It seems Method 1
doesn't need GROUP BY while Method 2 does, I am wondering if I have
more than 50 variables in the SELECT, will the GROUP BY in Method 2
create any potential problems? Thanks!

--Method 1--
select person_id, account, open_date
,min_open=(select min(open_date) from test_5 as X where
(X.person_id=Y.person_id
and account like '2%'))
from test_5 as Y
where account like '2%'

--Method 2--
select a.person_id, a.account, a.open_date, min(b.open_date)
from test_5 as a
join test_5 as b
on a.person_id=b.person_id
where a.account like '2%'
group by a.person_id, a.account, a.open_date

Jul 23 '05 #3
On 17 Mar 2005 06:24:54 -0800, ro******@gmail.com wrote:
Thanks Hugo! It works perfectly!

I also tried Method 2, which gave me the same result as your method. I
am more used to using JOIN function. But I am wondering if Method 1 has
any advantages over Method 2 in the long term.
Hi rong.guo,

That's hard to say from here. It depends on lots of factors. The best
way to find out which version is better in your situation, is to test
both (emptying the cache in between) and compare the execution times,
the output of SET STATISTICS IO ON and the execution plans used.

There's also a third method you can use:

SELECT a.person_id, a.account, a.open_date, b.min_open_date
FROM test_5 AS a
JOIN (SELECT person_id, MIN(open_date) AS min_open_date
FROM test_5
WHERE account LIKE '2%'
GROUP BY person_id) AS b
ON a.person_id = b.person_id
WHERE a.account LIKE '2%'

(snip)--Method 2--
select a.person_id, a.account, a.open_date, min(b.open_date)
from test_5 as a
join test_5 as b
on a.person_id=b.person_id
where a.account like '2%'
group by a.person_id, a.account, a.open_date


You'll have to add
AND b.account LIKE '2%'
to this query, otherwise it is not equivalent to query 1.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)
Jul 23 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

14 posts views Thread by atse | last post: by
2 posts views Thread by skura | last post: by
5 posts views Thread by pmud | last post: by
16 posts views Thread by D Witherspoon | last post: by
32 posts views Thread by Neil Ginsberg | 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.