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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

magmo
Aged Yak Warrior

558 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
2875 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

558 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
2875 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

558 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
2242 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  
 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.12 seconds. Powered By: Snitz Forums 2000