Comparing two rows to see if I need the data

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

Comparing two rows to see if I need the data

Author

Topic

kelemvor
Starting Member

6 Posts

Posted - 2011-06-03 : 14:34:44

Hi all,

I hope I'm able to explain this well enough to get some help.

I have a table with 3 fields I'm using.
Transaction_ID (self explanatory)
Sequence_Number (counter for each row that goes with each transaction in order)
String_Val (contains a Machine ID value I need, but not always)

Sample

1234 1 MID=102xxxxx
1234 2 blah
1235 1 MID=401xxxxx
1235 2 blah
1235 3 blah again
1236 1 MID=102xxx
1237 1 MID=102xxxx
1237 2 blah

At first I just needed to get a count of how many Sequences there were for each machine.
This I did pretty easily by using something like

SELECT substr(String_Val,5,3)
Count(*)
From table
Where String_Val like 'MID=%'
group by substr(String_Val,5,3)

Assuming I typed that all correctly, that part of it works fine and would return something like
MID Count
102 3
401 1

However, I then realized that we only need to count the rows when there is at least 2 Sequence_Numbers for the transaction. This is where I hit a wall.

I can't just query off the rows with a 2 because the Machine ID is only in the row with the 1.
So somehow I need to check for the row with a 1 to get the MID part and then see if there's also a corresponding row that has a 2 to know if I actually want to use that value or not.

So what I'd want the output from the above sample to be would be
MID Count
102 2
401 1

Showing that transaction 1236 would not be counted because there's only a 1 Sequence and not a 2 to go along with it.
Does that make sense? Can anyone help me wrap my brain around this? :)

Thanks!!

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-06-03 : 14:58:50

t-SQL doesn't have substr, but this should help you

DECLARE @TABLE TABLE (Transaction_ID int,Sequence_Number int,String_Val varchar(20))

INSERT INTO @TABLE
SELECT 1234, 1,'MID=102xxxxx' UNION ALL
SELECT 1234, 2,'blah' UNION ALL
SELECT 1235, 1,'MID=401xxxxx' UNION ALL
SELECT 1235, 2,'blah' UNION ALL
SELECT 1235, 3,'blah again' UNION ALL
SELECT 1236 ,1,'MID=102xxx' UNION ALL
SELECT 1237, 1,'MID=102xxxx' UNION ALL
SELECT 1237, 2,'blah'

SELECT substring(String_Val,5,3),count(*)

FROM @Table t1
WHERE String_Val like 'MID=%'
and exists
(select *
from @table t2
where t1.transaction_id = t2.transaction_id
and t2.Sequence_Number > t1.Sequence_Number
)
GROUP BY substring(String_Val,5,3)

Jim

Everyday I learn something that somebody else already knew

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources