SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 match 2 columns but third must differ
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

magmo
Aged Yak Warrior

519 Posts

Posted - 01/24/2013 :  09:12:36  Show Profile  Reply with Quote
Hi

I have a table structure lika this..



ID  | TID |  TStamp | No         |  FileName
1     122    100123   hn45          hn45.xml
2     123    100123   hn45          hn45.xml
3     124    100123   hn45          hn45.xml
4     222    200123   hn55          hn55.xml
5     222    200123   hn55          hn55.xml
6     122    100123   hn65          hn65.xml






I would like to filter out row ID 3 and 5 beacuse the columns "TStamp" and "No" are the same but the "TID" column is different. If you then look at ID 1, 2 and 3 they all fit in to that criteria, but I only want the latest record if the first criteria is filled. Hope this make sence, I really need some help with this...

jimf
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 01/24/2013 :  10:21:29  Show Profile  Reply with Quote
I couldn't tell if you actually wanted ID 3 in the result set or, that's why it's always a good idea to post your expected results. But based on when I could guess, this might work.

DECLARE @Table Table (ID int, TID int, TStamp int, Num char(4), FileNames char(8))
INSERT INTO @Table
VALUES
(1 , 122 , 100123,'hn45','hn45.xml'),
(2 , 123 , 100123,'hn45','hn45.xml'),
(3, 124 , 100123,'hn45','hn45.xml'),--
(4, 222 , 200123,'hn55','hn55.xml'),
(5, 222 , 200123,'hn55','hn55.xml'),--
(6, 122 , 100123,'hn65','hn65.xml')


SELECT t2.*
FROM
(
select t1.TStamp,t1.Num
from @table t1
group by t1.TStamp,t1.Num
) t1

OUTER APPLY
(select top 1 *
from @Table t2 where t1.TStamp = t2.TStamp and t1.Num = t2.num
order by t2.TID desc
) t2

ORDER BY 1

Jim

Everyday I learn something that somebody else already knew
Go to Top of Page

magmo
Aged Yak Warrior

519 Posts

Posted - 01/24/2013 :  10:59:05  Show Profile  Reply with Quote
The rows that are supposed to be in the result are ID 3 and 5
Go to Top of Page

jimf
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 01/24/2013 :  11:41:25  Show Profile  Reply with Quote
DECLARE @Table Table (ID int, TID int, TStamp int, Num char(4), FileNames char(8))
INSERT INTO @Table
VALUES
(1 , 122 , 100123,'hn45','hn45.xml'),
(2 , 123 , 100123,'hn45','hn45.xml'),
(3, 124 , 100123,'hn45','hn45.xml'),--
(4, 222 , 200123,'hn55','hn55.xml'),
(5, 222 , 200123,'hn55','hn55.xml'),--
(6, 122 , 100123,'hn65','hn65.xml')


SELECT DISTINCT t2.*
FROM
(
select t1.TStamp,t1.Num
from @table t1
group by t1.TStamp,t1.Num
) t1

CROSS APPLY
(select top 1 *
from @Table t2 where t1.TStamp = t2.TStamp
order by t2.TID desc
) t2


Jim

Everyday I learn something that somebody else already knew
Go to Top of Page

magmo
Aged Yak Warrior

519 Posts

Posted - 01/24/2013 :  12:09:59  Show Profile  Reply with Quote
Thanks, it works with your example but in my test enviroment I also get rows that doesn't have multiple matches on "TStamp" and "Num"
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2215 Posts

Posted - 01/28/2013 :  02:24:19  Show Profile  Reply with Quote
DECLARE @Table Table (ID int, TID int, TStamp int, Num char(4), FileNames char(8))
INSERT INTO @Table
VALUES
(1 , 122 , 100123,'hn45','hn45.xml'),
(2 , 123 , 100123,'hn45','hn45.xml'),
(3, 124 , 100123,'hn45','hn45.xml'),--
(4, 222 , 200123,'hn55','hn55.xml'),
(5, 222 , 200123,'hn55','hn55.xml'),--
(6, 122 , 100124,'hn65','hn65.xml')

see here TStamp(100124) occurred only once. So this row is also excluded from final output....


SELECT DISTINCT t2.*
FROM
(
select t1.TStamp,t1.Num
from @table t1
group by t1.TStamp,t1.Num
HAVING COUNT(*) >1
) t1

CROSS APPLY
(select top 1 *
from @Table t2 where t1.TStamp = t2.TStamp
order by t2.TID desc
) t2

--
Chandu
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000