All Forums Database
Vga 12 posts Joined 02/12
13 Jul 2016
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.

RATTLESKIN 2 posts Joined 07/16
13 Jul 2016

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>

Forever trust in who we are!

yuvaevergreen 93 posts Joined 07/09
14 Jul 2016

Can you post the expected result?

M.Saeed Khurram 544 posts Joined 09/12
14 Jul 2016
SELECT
LOCATION,
ORDER_ID,
CLASS,
DATE,
ROW_NUMBER() OVER (PARTITION BY  LOCATION, ORDER_ID  ORDER BY DATE) AS RANK
FROM
TABLE_NAME

 

Khurram

You must sign in to leave a comment.