CODEWITHSUNDEEP
sqlsql-server
Bishan
Bishan

Reputation: 15710

Increment and Change counter based on change in a column value

My query returns result like this. sqlfiddle

COLOR_NAME

RED
RED
RED
GREEN
GREEN
BLUE
WHITE
WHITE
WHITE
WHITE
WHITE
WHITE

I need to show number with above result. So the desired result is like this.

COLOR_NAME SORT_NO

RED         10
RED         11
RED         12
GREEN       10
GREEN       11
BLUE        10
WHITE       10
WHITE       11
WHITE       12
WHITE       13
WHITE       14
WHITE       15

How could I achieve this in ms sql?

Upvotes: 0

Views: 373

Answers (1)

Nenad Zivkovic
Nenad Zivkovic

Reputation: 18559

You can use ROW_NUMBER() function

select COLOR_NAME 
, 9 + ROW_NUMBER() OVER (PARTITION BY COLOR_NAME ORDER BY ID) AS Sort_No
from TB_COLOR

SQLFiddle

Upvotes: 3

Related Questions