CODEWITHSUNDEEP
sqlsql-server
Shane
Shane

Reputation: 542

Calculate how many rows are ahead of position in column when condition is met

How can I calculate how many people are ahead of Jane on Floor 2 (not including those on floor 1)? 

+------+---------+----------+
|Index |  Name   |   Floor  |
+------+---------+----------+
|   1  | Sally   |     1    |
|   2  | Sue     |     1    |
|   3  | Fred    |     1    |
|   4  | Wally   |     2    |
|   5  | Tommy   |     2    |
|   6  | Jane    |     2    |
|   7  | Bart    |     2    |
|   8  | Sam     |     3    |
+------+---------+----------+

The expected result is 2 as there are 2 people (Wally & Tommy) ahead of Jane on floor 2.

I've tried using CHARINDEX to find the row number from a temp table that I've generated but that doesn't seem to work:

SELECT CHARINDEX('Jane', Name) as position
INTO #test
FROM tblExample
WHERE Floor = 2
select ROW_NUMBER() over (order by position) from #test
WHERE position = 1

Upvotes: 0

Views: 71

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269663

You could do:

select count(*)
from t
where t.floor = 2 and
      t.id < (select t2.id from t t2 where t2.name = 'Jane' and t2.floor = 2);

With an index on (floor, name, id), I would expect this to be faster than row_number().

Upvotes: 0

John Cappelletti
John Cappelletti

Reputation: 81940

I think a simple row_number() would do the trick

Select Value = RN-1
 From  (
         Select *
               ,RN = row_number() over (partition by [floor] order by [index])
         From  YourTable
         Where [Floor]=2
       ) A
 Where [Name]='Jane'

Upvotes: 2

Related Questions