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 2005 Forums
 Transact-SQL (2005)
 match 2 columns but third must differ

Author  Topic 

magmo
Aged Yak Warrior

558 Posts

Posted - 2013-01-24 : 09:12:36
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
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2013-01-24 : 10:21:29
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

558 Posts

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

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2013-01-24 : 11:41:25
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

558 Posts

Posted - 2013-01-24 : 12:09:59
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
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-01-28 : 02:24:19
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
   

- Advertisement -