CODEWITHSUNDEEP
sqlsql-servergroup-byrow-number
Raju
Raju

Reputation: 207

row numbering based on the number of rows returned by query

I have requirement, where i have to give rownumber for each record returned by my query based on the total count of the rows returned. lets say a rownumber for each 3 record. for ex.

colA    colB colC(rowno)
1       abc  1
2       asd  1
3       asw  1
4       tre  2
5       cfr  2
6       dfr  2
7       sdf  3

I tried with row_number() over (partition by count(*) order by colA) but it gives the row number as 1 for all the records.

Any idea would help.

Upvotes: 0

Views: 314

Answers (2)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239724

Use some maths and the integer division rules:

select colA,colB,(ROW_NUMBER() OVER (ORDER BY colA)+2)/3 as colC
from table

The two integer constants are related - you always want the inner constant (2) to be one less than the number of rows which should be assigned the same number (3).

Upvotes: 3

Nitin S
Nitin S

Reputation: 7601

try this

SELECT ROW_NUMBER() OVER (Order by [Col]) as ColID FROM [TABLE NAME]
WHERE colC = 3

Upvotes: 0

Related Questions