How to generate serial number based on condition based in SQL server?

Question

I have one table name called "ABC".

"ABC" Table format :

Operation   Status    value
----------------------------
OP10          1        100
OP10          1        200
OP10          2        300
OP20          1        400
OP20          3        500
OP30          1        100
OP30          2        200

I need to generate serial number for based on Operation.

E.g: Expected Output

SNo   Operation   Status    Value
-----------------------------------
1      OP10          1        100
1      OP10          1        200
1      OP10          2        300
2      OP20          1        400
2      OP20          3        500
3      OP30          1        100
3      OP30          2        200

How to get the result as above formatted output.

Tim Biegeleisen · Accepted Answer · 2017-03-23 06:46:29Z

1

You can use DENSE_RANK()

SELECT DENSE_RANK() OVER (ORDER BY Operation) SNo,
       Operation, Status, Value
FROM yourTable
ORDER BY Value

Output:

Demo here:

Rextester

edited Mar 23, 2017 at 6:46

answered Mar 23, 2017 at 6:32

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to generate serial number based on condition based in SQL server?

1 Answer 1

Rextester

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related