• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 80
  • Last Modified:

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.
0
n f
Asked:
n f
  • 2
  • 2
1 Solution
 
PortletPaulfreelancerCommented:
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?
0
 
n fAuthor 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.
0
 
n fAuthor 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.
0
 
PortletPaulfreelancerCommented:
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)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now