CODEWITHSUNDEEP
sql-serversequencerow-number
SuicideSheep
SuicideSheep

Reputation: 5550

How to query records based on row_num and one of the column value?

Rownum  Status
1         2
2         1
3         3
4         2
5         3
6         1

The condition is to query records appear before the first record of status=3 which in the above scenario the expected output will be rownum = 1 and 2.
In the case if there is no status=3 then show everything.

I'm not sure from where to start hence currently no findings

Upvotes: 0

Views: 26

Answers (1)

Giorgos Betsos
Giorgos Betsos

Reputation: 72175

If you are using SQL Server 2012+, then you can use window version of SUM with an ORDER BY clause:

SELECT Rownum, Status
FROM (
  SELECT Rownum, Status, 
         SUM(CASE WHEN Status = 3 THEN 1 ELSE 0 END) 
          OVER 
           (ORDER BY Rownum) AS s
FROM mytable) t
WHERE t.s = 0

Calculated field s is a running total of Status = 3 occurrences. The query returns all records before the first occurrence of a 3 value.

Demo here

Upvotes: 1

Related Questions