match 2 columns but third must differ

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

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

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

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"

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources