CODEWITHSUNDEEP
sqlsql-server-2008t-sql
Simon Pawlowski
Simon Pawlowski

Reputation: 45

Count rows belonging together

I have to create a Procedure filling in discount values on higher shipping amounts.

In my Table there are all article numbers, the amount of orders needed for the discount and the discount.

I want to have the article number + a counting value of the amount of orders needed just for the CASE WHEN in my update.

Right now I only get the rownumber of the complete select:

IB-000074   53
IB-000074   8487
IB-000074   8592
IB-000075   8593
IB-000075   54
IB-000075   8486

I need to display something like this:

IB-000074   1
IB-000074   2
IB-000074   3
IB-000075   1
IB-000075   2
IB-000075   3

Anyone knows how to replace the lowest value with 1, the 2nd lowest with 2 and so on?

Upvotes: 1

Views: 98

Answers (2)

vhadalgi
vhadalgi

Reputation: 7189

Refer Link: Row_number()

Refer Link: Over clause

You could use

row_number()over(partition by ID order by something)

Refer Link: Example

Upvotes: 1

Radu Gheorghiu
Radu Gheorghiu

Reputation: 20509

You can use an ordering function, like

ROW_NUMBER() OVER (PARTITION BY ID ORDER BY AMOUNT)

Upvotes: 1

Related Questions