CODEWITHSUNDEEP
sqlsql-serverselect
Nicolaesse
Nicolaesse

Reputation: 2714

Row_number over partition and find the max rn value

I started from the following table:

+---+-----+---------+----------+----------+-------------+
| 1 | ID  | OrderNO | PartCode | Quantity | DateEntered |
| 2 | 417 | 2144    | 44917    | 100      | 40773       |
| 3 | 418 | 7235    | 11762    | 5        | 40773       |
| 4 | 419 | 9999    | 60657    | 100      | 40773       |
| 5 | 420 | 9999    | 60657    | 90       | 40774       |
+---+-----+---------+----------+----------+-------------+

to this:

+---+---------+----------+----------+-------------+----+
| 1 | OrderNO | PartCode | Quantity | DateEntered | rn |
| 2 | 2144    | 44917    | 100      | 40773       | 1  |
| 3 | 7235    | 11762    | 5        | 40773       | 1  |
| 4 | 9999    | 60657    | 100      | 40773       | 1  |
| 5 | 9999    | 60657    | 90       | 40774       | 2  |
+---+---------+----------+----------+-------------+----+

using the query fo this answer.

select OrderNO,PartCode,Quantity,row_number() over(partition by OrderNO order by DateEntered desc) as rn
from YourTable) as T

Now, I am trying to add a field "rn_max", shich is the max "rn" of each OrderNO and get the following result:

+---+---------+----------+----------+-------------+----+--------+
| 1 | OrderNO | PartCode | Quantity | DateEntered | rn | rn_max |
| 2 | 2144    | 44917    | 100      | 40773       | 1  | 1      |
| 3 | 7235    | 11762    | 5        | 40773       | 1  | 1      |
| 4 | 9999    | 60657    | 100      | 40773       | 1  | 2      |
| 5 | 9999    | 60657    | 90       | 40774       | 2  | 2      |
+---+---------+----------+----------+-------------+----+--------+

I could get this result calculating the max of each OrderNO with the query

SELECT OrderNO,MAX(rn) AS 'rn_max'
FROM (
select OrderNO,PartCode,Quantity,row_number() over(partition by OrderNO order by DateEntered desc) as rn
from YourTable
) T
Group by OrderNO

and then use a left join from YourTable. Is there a shortest way to count the rows with the same OderNo? I've tried to add row_number() over(partition by OrderNO) as rn_max at the query but I need to write an order by clause.

Upvotes: 2

Views: 15033

Answers (2)

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

Use max window function.

SELECT T.*,MAX(rn) OVER(PARTITION BY OrderNo) AS rn_max
FROM (
select OrderNO,PartCode,Quantity,row_number() over(partition by OrderNO order by DateEntered desc) as rn
from YourTable
) T

Edit: An easier option is to use count as suggested by @Jason A. Long in the comments. 

select OrderNO
       ,PartCode
       ,Quantity
       ,row_number() over(partition by OrderNO order by DateEntered desc) as rn
       ,count(*) over(partition by OrderNO) as maxrn
from YourTable

Upvotes: 6

DhruvJoshi
DhruvJoshi

Reputation: 17126

you can try this. Basically you'd use max() aggregation over the partitioned rows to get the rn_max

select *, max(rn) over(partition by OrderNO)  as rn_max
FROM (
select OrderNO,PartCode,Quantity,row_number() over(partition by OrderNO order by DateEntered desc) as rn
from YourTable
) T

Upvotes: 1

Related Questions