We help IT Professionals succeed at work.

RANK() or possibly ROW_NUMBER() question

Assume a table with 5 columns

CREATE TABLE Table1
 
(ID INT IDENTITY(1,1) NOT NULL,
 
COL_A INT,
 
COL_B INT,

COL_C DATETIME2,

COL_D INT)
 
INSERT INTO Table1 (COL_A, COL_B, COL_C, COL_D) VALUES ( -6666, 6666, '2015-02-27 19:00', 1)
INSERT INTO Table1 (COL_A, COL_B, COL_C, COL_D) VALUES ( -7777, 7777, '2015-02-27 20:00', 1)
INSERT INTO Table1 (COL_A, COL_B, COL_C, COL_D) VALUES ( -8888, 8888, '2015-02-27 20:00', 1)

SELECT * FROM Table1


ID      COL_A      COL_B      COL_C                                              COL_D
1      -6666      6666      2015-02-27 19:00:00.0000000      1
2      -7777      7777      2015-02-27 20:00:00.0000000      1
3      -8888      8888      2015-02-27 20:00:00.0000000      1

I need to be able to rank (or row number) these so that the Rank will look like the following:
ID      COL_A      COL_B      COL_C                                             COL_D      RANKED
1      -6666      6666      2015-02-27 19:00:00.0000000      1            1
3      -8888      8888      2015-02-27 20:00:00.0000000      1            1
2      -7777      7777      2015-02-27 20:00:00.0000000      1            2

Notice that COL_C is the same for IDs 2 & 3, but different for ID 1.

All my attempts to rank these correctly fail. How do I do this?

I am using SQL Server 2014, but I think the syntax has been the same for RANK() since SQL Server 2008.
Comment
Watch Question

PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
what is it that makes these 2 rows the same rank?
there has to be something "in common" for them to be ranked the same

ID      COL_A      COL_B      COL_C                                    COL_D      RANKED
1      -6666      6666      2015-02-27 19:00:00.0000000      1            1
3      -8888      8888      2015-02-27 20:00:00.0000000      1            1

i.e. what is it that you said to yourself when you placed that 1 under under the ranked heading. why are they 1?
n f

Author

Commented:
Multiple records can come in at the same time, so they will have the same timestamp. Of these same-timestamp records, the values in COL_D may be the same, but the values in COL_B will always be different.

To rank two or more records with the same values in COL_C and COL_D, COL_B should be used, with rank in descending order. The other fields need to be returned, as well.

In my sample data, only one record, ID = 1, has a smaller timestamp and also a 1 in COL_D. So ID = 1 gets a rank of 1.

Both records  ID = 2 and ID = 3 have the same value in COL_C and COL_D. To rank them, COL_B must be used. Since 8888 > 7777, ID = 3 gets a rank of 1 and ID = 2 gets a rank of 2.
n f

Author

Commented:
I should have said, "In my sample data, only one record, ID = 1, has a DISTINCT timestamp and also a 1 in COL_D. So ID = 1 gets a rank of 1."

It's not just that the timestamp in COL_C is smaller for ID = 1, it's that it is unique amongst these records.

ID = 2 and ID = 3 don't have unique timestamps or values in COL_D, but can only be ranked by COL_B.
EE Topic Advisor
Most Valuable Expert 2014
Awarded 2013
Commented:
have you tried:

ROW_NUMBER() OVER(PARTITION BY COL_C ORDER BY COL_D, COL_B DESC)

I have no dbms access right now so unable to verify anything (darn it)