CODEWITHSUNDEEP
sqlsql-server-2008
David Kethel
David Kethel

Reputation: 2550

How to select all records after a certain record within groups in sql?

Given the following table structure

Col1, Col2, EventType,  DateTime

How can I select the records per grouping of Col1, Col2 that occur after the top record where EventType = 3 for that particular group of Col1, Col2.

For example with the following data 

Col1, Col2, EventType, DateTime
A       B       1      2012-1-1
A       B       3      2011-1-1
A       B       1      2010-1-1
C       D       1      2012-1-1
C       D       2      2011-1-1
C       D       2      2010-1-1
C       D       3      2009-1-1
C       D       2      2008-1-1
C       D       3      2007-1-1
C       D       1      2006-1-1
C       D       2      2005-1-1

I want to select

Col1, Col2, EventType, DateTime
A       B       1      2012-1-1

C       D       1      2012-1-1
C       D       2      2011-1-1
C       D       2      2010-1-1

Upvotes: 2

Views: 1625

Answers (5)

Andriy M
Andriy M

Reputation: 77667

It is possible to solve this using ROW_NUMBER():

  1. Partition the rows into groups of (Col1, Col2) and rank rows in every group in the ascending order of DateTime.

    Col1  Col2  EventType  DateTime  EventRank
----  ----  ---------  --------  ---------
A     B     1          2012-1-1  3
A     B     3          2011-1-1  2
A     B     1          2010-1-1  1
C     D     1          2012-1-1  8
C     D     2          2011-1-1  7
C     D     2          2010-1-1  6
C     D     3          2009-1-1  5
C     D     2          2008-1-1  4
C     D     3          2007-1-1  3
C     D     1          2006-1-1  2
C     D     2          2005-1-1  1

  2. Also, partition the rows by (Col1, Col2, EventType) and rank them in the descending order of DateTime.

    Col1  Col2  EventType  DateTime  EventRank  EventSubRank
----  ----  ---------  --------  ---------  ------------
A     B     1          2012-1-1  3          1
A     B     3          2011-1-1  2          1
A     B     1          2010-1-1  1          2
C     D     1          2012-1-1  8          1
C     D     2          2011-1-1  7          1
C     D     2          2010-1-1  6          2
C     D     3          2009-1-1  5          1
C     D     2          2008-1-1  4          3
C     D     3          2007-1-1  3          2
C     D     1          2006-1-1  2          2
C     D     2          2005-1-1  1          4

  3. Select a subset where EventType = 3 AND EventSubRank = 1.

    Col1  Col2  EventType  DateTime  EventRank  EventSubRank
----  ----  ---------  --------  ---------  ------------
A     B     3          2011-1-1  2          1
C     D     3          2009-1-1  5          1

  4. Use it as a filter by joining it back to the ranked row set and selecting rows of the latter whose EventRank values are greater than the corresponding ones in the subset.

Here's a complete query:

WITH ranked AS (
  SELECT
    *,
    EventRank    = ROW_NUMBER() OVER (PARTITION BY Col1, Col2            ORDER BY DateTime ASC ),
    EventSubRank = ROW_NUMBER() OVER (PARTITION BY Col1, Col2, EventType ORDER BY DateTime DESC)
  FROM atable
),
filtered AS (
  SELECT *
  FROM ranked
  WHERE EventType    = 3
    AND EventSubRank = 1
)
SELECT
  r.Col1,
  r.Col2,
  r.EventType,
  r.DateTime
FROM ranked
INNER JOIN filtered f
   ON r.Col1 = f.Col1
  AND r.col2 = f.Col2
  AND r.EventRank > f.EventRank
;

Upvotes: 1

Maximus
Maximus

Reputation: 812

Try This,

Select a.* FROm yourtable A JOIN

(SELECT * FROM yourTable WHERE EventType=3) b
ON A.Col1=B.Col1
AND A.Col2 = B.Col2
WHERE A.dateTime>B.DateTime

Upvotes: 0

Sam Bauwens
Sam Bauwens

Reputation: 1367

You can use the max function over a subquery:

SELECT Col1, Col2, EventType, DateTime 
FROM theTable A
WHERE DateTime > 

(SELECT  MAX(DateTime)
FROM    theTable SUB
WHERE   EventType = 3
AND SUB.COL1 = A.COL1
AND SUB.COL2 = A.COL2)

Upvotes: 3

Sandeep Kumar
Sandeep Kumar

Reputation: 803

To get the expected result 

       Select   Col1, Col2, EventType, DateTime  from table where EventType<3

Upvotes: 0

diwatu
diwatu

Reputation: 5699

select Col1, Col2, EventType, DateTime 
From yourtable A, 
(select Col1, Col2 from yourtable  group by Col1, Col2) B 
where A.EventType<3 and A.Col1 =B.Col1 And A.Col2=B.Col2

Upvotes: 0

Related Questions