Compare rows inside the same results of the table data.

Question

Hi,

I came through a intresting situationd and would request your suggestions to acheive it.

I have a table MET with columns ID,Type,Time & Session which when queried through some conditions will result sumthg like this:

ID Type Time Session

12 VS 10:58 XYZ

34 VD 10:58 XYZ

56 VK 10:58 XYZ

78 VS 11:04 ABC

90 VD 11:04 ABC

21 VS 11:10 DEF

22 VD 11:10 DEF

23 VK 11:10 DEF

So, I m concerned only pattern where VK comes in a session after VS & VD rest of them like 78 & 90 i don't care. Once VK comes i need to chk the previous row VS and make sure it has the same session as it has i.e.,XYZ equal to XYZ and DEF equal to DEF.

Please share your options to acheive it.

Thanks for your time and appreciate it.

Regards,

Ram

Answer 1

Does VS have to be the row before VK or just an ID that's less than VK for that session? If it's ok for VS and VD just to have a lower ID than VK I would do this following (if it just has to be the same session you can get rid of the 2nd AND statement in each JOIN):

IF OBJECT_ID('tempdb..#ValidSessions') IS NOT NULL DROP TABLE #ValidSessions

SELECT DISTINCT m.SESSION
INTO #ValidSessions
FROM MET AS m
 INNER JOIN MET AS m2
 ON m.SESSION = m2.SESSION
 AND m2.Type = 'VD' 
 AND m.ID > m2.ID
 INNER JOIN MET AS m3
 ON m.SESSION = m3.SESSION
 AND m3.Type = 'VS'
 AND m.ID > m3.ID
WHERE m.Type = 'VK'

SELECT * 
FROM MET AS m
 INNER JOIN #ValidSessions AS vs
 ON m.SESSION = vs.SESSION

Compare rows inside the same results of the table data.

Follow this question

Related Questions