Query for Specific Transaction

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

General SQL Server Forums

New to SQL Server Programming

Query for Specific Transaction

Author

Topic

jbrown7232
Starting Member

22 Posts

Posted - 2009-12-18 : 12:18:40

Hello I have two columns that are Ints(TransactionID and ServiceID) and I am trying to find all transactions
that have serviceID of only 4 and there are transactions with multiple numbers including the number 4, I dont want it.
There are no constraints on either column so there can be multiple of each.

Example

TransactionID ServiceID
1 1
1 4
2 4
3 2
3 3
3 4
5 4

So i just want TransactionID of 2 and 5 in this case

Hope this makes sense.

TIA

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-12-18 : 12:27:47

If you are using SQL server 2005 or above

select TransactionID from
(
select row_number() over (partition by TransactionID order by ServiceID) as seq,*
from table1
) t
where t.seq = 1 and t.ServiceID = 4

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-12-18 : 12:33:24

Just realized..if there were another 2 rows with 6,4 and 6,5 the above wudn't work...

SO..Try this too..

select a.TransactionID from 
table1 a inner join (
select TransactionID from table1 group by TransactionID having count(TransactionID) = 1
) b
on a.TransactionID = b.TransactionID and a.ServiceID = 4

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources