distinct record - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

distinct record

Author

Topic

jung1975
Aged Yak Warrior

503 Posts

Posted - 2004-11-16 : 13:33:52

I have a table:


date          store_ID  P_Id
01/01/2004      1001    3456
10/30/2003      1003    3456
04/31/2004      1005    5678

How do I select a p_id with most recent date(and store_id)?
the output should look like:


date          store_ID  P_Id
01/01/2004      1001    3456
04/31/2004      1005    5678

VIG
Yak Posting Veteran

86 Posts

Posted - 2004-11-16 : 14:14:22

[code]select * from YourTable t
where date=(select max(date) from YourTable t2
where t2.p_id =t1.p_id)[/code]

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-11-16 : 14:15:39

quote:

Originally posted by VIG

select * from YourTable t1
where date=(select max(date) from YourTable t2 
            where t2.p_id =t1.p_id)

Corey

VIG
Yak Posting Veteran

86 Posts

Posted - 2004-11-16 : 14:20:35

Thanks

jung1975
Aged Yak Warrior

503 Posts

Posted - 2004-11-16 : 15:48:32

Actually, the table looks more like this..


date          store_ID  P_Id
01/01/2004      1001    3456
10/30/2003      1003    3456
04/31/2004      1005    5678
08/14/2004      1005    5678
10/07/2004      1005    7890

and the output should return:


date          store_ID  P_Id
01/01/2004      1001    3456
08/14/2004      1005    5678
10/07/2004      1005    7890

So, your query won't work quite right...
Can you help me out again?

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-11-16 : 16:06:20

[code]
select
date = max(date),
store_id,
p_id
from YourTable
Group By store_id, p_id
[/code]

Corey

jung1975
Aged Yak Warrior

503 Posts

Posted - 2004-11-16 : 16:58:21

your query won't work.. your query will return


dt                        store_id     p_id
2004-01-01 00:00:00.000	   1001      	3456      
2003-10-31 00:00:00.000	   1003      	3456 ---> This record should be excluded     
2004-08-14 00:00:00.000	   1005      	5678      
2004-10-07 00:00:00.000	   1005      	7890

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-11-16 : 23:22:21

declare @t table(cdate smalldatetime,store_ID int, P_Id int)

insert into @t
select convert(smalldatetime,'1/1/2004'),1001 ,3456
union select
convert(smalldatetime,'10/30/2003'),1003 , 3456
union select
convert(smalldatetime,'4/30/2004'),1005 , 5678
union select
convert(smalldatetime,'8/14/2004'),1005 , 5678
union select
convert(smalldatetime,'10/7/2004'),1005 ,7890

select t.cdate,t.store_id,t.p_id
from @t t
join (select max(cdate) as [cdate],p_id from @t group by p_id) t1
on t.cdate=t1.cdate

--------------------
keeping it simple...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources