Rank Sequence by Group

Question

Rank Sequence by Group

Matt Hinsley

SSC Journeyman

Points: 97
More actions
July 27, 2007 at 4:44 pm

#120890

I know this can be done in SQL 2005 using PARTITION and OVER but I need to do this in SQL 2000. Here goes...
I have a table as follows;
Jobno decimal(6,0)
Status char(2)
Date datetime
Job    Status Date
1234 AD       01/06/07
1234 AD       02/06/07
1234 AD       03/06/07
2345 AD       01/07/07
2345 AS       01/07/07
3456 AE       02/03/07
3456 AE       03/07/07
6789 AI        01/07/07
6789 AI        06/06/07
6789 AI        07/07/07
8910 ZZ       10/07/07
Note: There are a maximum of 4 records for each combination of Job and Status
I want to be able to create a "ranked sequence" column for each instance of grouped Job + Status where the most recent date of a job will be ranked at 1 incrementing by 1 for however many instances of the same Job + Status key

The resulting record set should look like this;
Job   Status Date        Seq
1234 AD       01/06/07 3
1234 AD       02/06/07 2
1234 AD       03/06/07 1
2345 AD       01/07/07 1
2345 AS       01/07/07 1
3456 AE       02/03/07 2
3456 AE       03/07/07 1
6789 AI       01/07/07 2
6789 AI       06/06/07 3
6789 AI       07/07/07 1
8910 ZZ      10/07/07 1
Thanks in advance.

Viewing 6 posts - 1 through 5 (of 5 total)

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

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 1

This will not produce the results you expect if there is a tied date for any give Job/Status combo... nor is it very good on really large tables. But it does work... solution below includes your test data...

--===== Create a table and test data (wish folks would post it this way, originally)

CREATE TABLE #yourtable

(Job INT, Status VARCHAR(5), Date DATETIME)

INSERT INTO #yourtable

(Job, Status, Date)

SELECT '1234','AD','01/06/07' UNION ALL

SELECT '1234','AD','02/06/07' UNION ALL

SELECT '1234','AD','03/06/07' UNION ALL

SELECT '2345','AD','01/07/07' UNION ALL

SELECT '2345','AS','01/07/07' UNION ALL

SELECT '3456','AE','02/03/07' UNION ALL

SELECT '3456','AE','03/07/07' UNION ALL

SELECT '6789','AI','01/07/07' UNION ALL

SELECT '6789','AI','06/06/07' UNION ALL

SELECT '6789','AI','07/07/07' UNION ALL

SELECT '8910','ZZ','10/07/07'

--===== Solve the problem

SELECT Job,

Status,

Date,

RankSeq = (SELECT COUNT(*)

FROM #yourtable i

WHERE i.Job = o.Job

AND i.Status = o.Status

AND o.Date <= i.Date)

FROM #yourtable o

ORDER BY o.Job, o.Status, o.Date

Here's the results...

Job Status Date RankSeq

----------- ------ ----------------------- -----------

1234 AD 2007-01-06 00:00:00.000 3

1234 AD 2007-02-06 00:00:00.000 2

1234 AD 2007-03-06 00:00:00.000 1

2345 AD 2007-01-07 00:00:00.000 1

2345 AS 2007-01-07 00:00:00.000 1

3456 AE 2007-02-03 00:00:00.000 2

3456 AE 2007-03-07 00:00:00.000 1

6789 AI 2007-01-07 00:00:00.000 3

6789 AI 2007-06-06 00:00:00.000 2

6789 AI 2007-07-07 00:00:00.000 1

8910 ZZ 2007-10-07 00:00:00.000 1

(11 row(s) affected)

Post back if that doesn't do it for you.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Matt Hinsley SSC Journeyman Points: 97 More actions · Answer 2

Matt Hinsley

SSC Journeyman

Points: 97

July 28, 2007 at 2:38 am

#723124

Thats perfect, exactly what I needed, thanks Jeff

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 3

You bet... thanks for the feedback, Matt.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 4

I wonder about the speed for your correlated subquery versus your patented triangular join?

With a larger table than below...

-- Prepare sample data

DECLARE @Sample TABLE (JobNo INT, Status CHAR(2), Date DATETIME)

SET DATEFORMAT MDY

INSERT @Sample

SELECT 1234, 'AD', '01/06/07' UNION ALL

SELECT 1234, 'AD', '02/06/07' UNION ALL

SELECT 1234, 'AD', '03/06/07' UNION ALL

SELECT 2345, 'AD', '01/07/07' UNION ALL

SELECT 2345, 'AS', '01/07/07' UNION ALL

SELECT 3456, 'AE', '02/03/07' UNION ALL

SELECT 3456, 'AE', '03/07/07' UNION ALL

SELECT 6789, 'AI', '01/07/07' UNION ALL

SELECT 6789, 'AI', '06/06/07' UNION ALL

SELECT 6789, 'AI', '07/07/07' UNION ALL

SELECT 8910, 'ZZ', '10/07/07'

-- Jeff's subquery

SELECT JobNo,

Status,

Date,

(

SELECT COUNT(*)

FROM @Sample AS i

WHERE i.JobNo = o.JobNo

AND i.Status = o.Status

AND o.Date <= i.Date

) AS RankSeq

FROM @Sample AS o

ORDER BY o.JobNo,

o.Status,

o.Date

-- Jeff's triangular join

SELECT s1.JobNo,

s1.Status,

s1.Date,

COUNT(*) AS RankSeq

FROM @Sample AS s1

INNER JOIN @Sample AS s2 ON s2.JobNo = s1.JobNo

AND s2.Status = s1.Status

AND s2.Date >= s1.Date

GROUP BY s1.JobNo,

s1.Status,

s1.Date

ORDER BY s1.JobNo,

s1.Status,

s1.Date

N 56°04'39.16"
E 12°55'05.25"

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 5

Heh... triangular joins have been around a lot longer than I've been writing SQL so I can't take any credit for them. Actually, I don't like either method just because they do both use triangular joins. They can be useful and fairly effective if the groups are small, but I wrote a fairly extensive reply to someone a couple of months ago about the evils of triangular joins.

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=338&messageid=359124&p=2

In fact, triangular joins can be hundreds of times worse than a cursor/While loop, if you're not careful...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)