Reassign sequential group numbers to results of DENSE_RANK?

Question

Reassign sequential group numbers to results of DENSE_RANK?

gcresse

SSCrazy

Points: 2286
More actions
April 13, 2016 at 11:16 am

#400015

I'm using DENSE_RANK to group multiple rows based on dates. The problem I have is some of the dates are null and that causes the rank to be 1. The DENSE_RANK does separate my rows into groups as I want, but I need to re-assign sequential numbers to the DENSE_RANK results, if that's possible. Here's a simple example of my issue:
CREATE TABLE [dbo].[TestGroup](
[testID] [int] IDENTITY(1,1) NOT NULL,
[testGroup] [int] NOT NULL
)
INSERT INTO TestGroup SELECT 1
INSERT INTO TestGroup SELECT 1
INSERT INTO TestGroup SELECT 2
INSERT INTO TestGroup SELECT 2
INSERT INTO TestGroup SELECT 1
INSERT INTO TestGroup SELECT 3
INSERT INTO TestGroup SELECT 4
SELECT * FROM TestGroup
The results from the SELECT statement look like this:
testIDtestGroup
11
21
32
42
51 <<< 1 because the original date used in the DENSE_RANK was null
63
74
But I need a new group number like this:
testIDtestGroup NewGroup
1 1 1
2 1 1
3 2 2
4 2 2
5 1 3
6 3 4
7 4 5
Is there a simple way to select from these results and re-sequence the testGroup to match the NewGroup?
Thanks!
Gina

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

drew.allen SSC Guru Points: 76997 More actions · Answer 1

It's probably going to be much easier to work from the raw data rather than pre-processed data. Can you post samples from the original table and the original query?

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

gcresse SSCrazy Points: 2286 More actions · Answer 2

Going back to a sample where I'm using the DENSE_RANK, here is another simple example:

CREATE TABLE [dbo].[testGroup](

[testRowID] [int] IDENTITY(1,1) NOT NULL,

[testLotID] varchar(5) NOT NULL,

[testDateTime] [datetime] NULL,

[testDays] [int] NOT NULL

)

INSERT INTO TestGroup (

testLotID

,testDateTime

,testDays)

SELECT 'Lot1'

,NULL

,0

INSERT INTO TestGroup (

testLotID

,testDateTime

,testDays)

SELECT 'Lot1'

,NULL

,0

INSERT INTO TestGroup (

testLotID

,testDateTime

,testDays)

SELECT 'Lot1'

,'10/15/15 7:13 PM'

,0

INSERT INTO TestGroup (

testLotID

,testDateTime

,testDays)

SELECT 'Lot1'

,'10/15/15 7:13 PM'

,4

INSERT INTO TestGroup (

testLotID

,testDateTime

,testDays)

SELECT 'Lot1'

,'10/15/15 7:13 PM'

,4

INSERT INTO TestGroup (

testLotID

,testDateTime

,testDays)

SELECT 'Lot1'

,'10/15/15 7:13 PM'

,55

INSERT INTO TestGroup (

testLotID

,testDateTime

,testDays)

SELECT 'Lot1'

,'10/15/15 7:13 PM'

,64

INSERT INTO TestGroup (

testLotID

,testDateTime

,testDays)

SELECT 'Lot1'

,'10/15/15 7:13 PM'

,89

INSERT INTO TestGroup (

testLotID

,testDateTime

,testDays)

SELECT 'Lot1'

,NULL

,0

INSERT INTO TestGroup (

testLotID

,testDateTime

,testDays)

SELECT 'Lot1'

,NULL

,0

INSERT INTO TestGroup (

testLotID

,testDateTime

,testDays)

SELECT 'Lot1'

,'1/12/16 1:27 PM'

,0

INSERT INTO TestGroup (

testLotID

,testDateTime

,testDays)

SELECT 'Lot1'

,NULL

,0

INSERT INTO TestGroup (

testLotID

,testDateTime

,testDays)

SELECT 'Lot1'

,'1/12/16 1:27 PM'

,76

SELECT testLotID

,testDateTime

,testDays

,DENSE_RANK() OVER (PARTITION BY testLotID ORDER BY testDateTime, testDays) testGroup

FROM TestGroup

ORDER BY testRowID

The results of the SELECT statement look like this:

testLotIDtestDateTime testDaystestGroup

Lot1NULL 0 1

Lot12015-10-15 19:13:00.000 0 2

Lot12015-10-15 19:13:00.000 4 3

Lot12015-10-15 19:13:00.000 55 4

Lot12015-10-15 19:13:00.000 64 5

Lot12015-10-15 19:13:00.000 89 6

Lot1NULL 0 1

Lot12016-01-12 13:27:00.000 0 7

Lot1NULL 0 1

Lot12016-01-12 13:27:00.000 76 8

but I really want the testGroup to look like the newGroup:

testLotIDtestDateTime testDaystestGroup NewGroup

Lot1NULL 0 1 1

Lot12015-10-15 19:13:00.000 0 2 2

Lot12015-10-15 19:13:00.000 4 3 3

Lot12015-10-15 19:13:00.000 55 4 4

Lot12015-10-15 19:13:00.000 64 5 5

Lot12015-10-15 19:13:00.000 89 6 6

Lot1NULL 0 1 7

Lot12016-01-12 13:27:00.000 0 7 8

Lot1NULL 0 1 9

Lot12016-01-12 13:27:00.000 76 8 10

Phil Parkin SSC Guru Points: 246987 More actions · Answer 3

Ordering the output by the IDENTITY column makes this a challenge, as I am sure you are aware.

What is the difference between the first batch of NULLs and the second and third? To me, it looks like they should be in the same group.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

drew.allen SSC Guru Points: 76997 More actions · Answer 4

Tables represent sets, which are inherently unordered. All of the rows that contain NULL values are indistinguishable from each other based solely on the data that you have provided, so there is no way to enforce a particular order given the data that you have provided.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Luis Cazares SSC Guru Points: 183706 More actions · Answer 5

Have you tried a Quirky Update? That would be my choice.

Reference: http://www.sqlservercentral.com/articles/T-SQL/68467/

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

gcresse SSCrazy Points: 2286 More actions · Answer 6

Thanks Luis. The Quirky Update worked perfectly 🙂

drew.allen SSC Guru Points: 76997 More actions · Answer 7

You need to be VERY CAREFUL with the quirky update. It is undocumented, not guaranteed, and requires a clustered index on the sort order. If you DO have a clustered index on the sort order, you are better off using that sort order to produce your dense rank.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Luis Cazares SSC Guru Points: 183706 More actions · Answer 8

drew.allen (4/14/2016)
You need to be VERY CAREFUL with the quirky update. It is undocumented, not guaranteed, and requires a clustered index on the sort order. If you DO have a clustered index on the sort order, you are better off using that sort order to produce your dense rank.
Drew

I agree on being very careful, follow the rules and test thoroughly. I wouldn't agree on adding work to the server when it's not necessary.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

drew.allen SSC Guru Points: 76997 More actions · Answer 9

Luis Cazares (4/14/2016)
drew.allen (4/14/2016)
You need to be VERY CAREFUL with the quirky update. It is undocumented, not guaranteed, and requires a clustered index on the sort order. If you DO have a clustered index on the sort order, you are better off using that sort order to produce your dense rank.
Drew
I agree on being very careful, follow the rules and test thoroughly. I wouldn't agree on adding work to the server when it's not necessary.

I would say that being able to guarantee the results is a huge (potentially overriding) factor in determining whether it is necessary.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA