Need to generate a row number like column

Database
Vga
Enthusiast

Need to generate a row number like column

As per below data, I need to generate a rank or row number type column based upon the following scenarios:

1) For a Location-Order-ID combination, it should be a same number irrespective of other columns (like Class/Date in this example).

2) Whenever the above mentioned combination changes, the rank columns should start in increasing order and must not reset to 1 again.

Location Order ID Class Date  Rank
70 ABC 123 90 07/13 1
70 ABC 123 89 07/13 1
70 DEF 456 80 07/15 2
77 GHI 789 78 07/20 3
77 MNO 678 75 07/12 4
77 MNO 567 70 07/20 5

I tried multiple combination of Rank and other functions but no success.

Any help is much appreciated.

Thanks.

Tags (2)
3 REPLIES

Re: Need to generate a row number like column

For solution to youre query 1. 2nd question is not clear to me

SELECT

LOCATION,

ORDER_ID,

CLASS,

DATE,

ROW_NUMBER() OVER (PARTITION BY  LOCATION, ORDER_ID  ORDER BY LOCATION, ORDER_ID) AS RANK

FROM

<TABLES>

Enthusiast

Re: Need to generate a row number like column

Can you post the expected result?

Enthusiast

Re: Need to generate a row number like column

SELECT
LOCATION,
ORDER_ID,
CLASS,
DATE,
ROW_NUMBER() OVER (PARTITION BY LOCATION, ORDER_ID ORDER BY DATE) AS RANK
FROM
TABLE_NAME
Khurram