Ranking rows in a group (SQL 2000)

Question

Post reply

Ranking rows in a group (SQL 2000)

webrunner

SSC-Dedicated

Points: 31890

March 30, 2007 at 2:52 pm

#153502

Hello,

I am trying to create session numbers for a course, such that the first meeting date/time gets Session 1, the second date/time gets Session 2, and so on. But I want the numbering to restart for each course. Something like this:

course	session	starttime	endtime
100	1	3/30/07 1:00 PM	3/30/2007 3:00 PM
100	2	4/7/07 1:00 PM	4/7/2007 3:00 PM
100	3	4/14/07 1:00 PM	4/14/2007 3:00 PM
200	1	4/1/07 1:00 PM	4/1/2007 3:00 PM
200	2	4/8/07 1:00 PM	4/8/2007 3:00 PM
200	3	4/15/07 1:00 PM	4/15/2007 3:00 PM

I did some searching on the web and learned 2 things:

1. One way to do this in SQL 2000 is:

select cola,colb, rank = (select count(*) + 1 from tablename t2 where t1.cola = t2.cola

and t2.colb > t1.colb)

from tablename t1

But this took forever on my recordset (which has about 900 rows but will sometimes reach about 2000 rows) - I canceled the query after 4 minutes.

2. There is a way to do this in SQL 2005. That way is described here:

Returning Ranked Results with Microsoft SQL Server 2005

http://www.4guysfromrolla.com/webtech/010406-1.shtml

Specifically,

SELECT c.Name, o.DateOrdered, tab.TotalOrderAmount,

ROW_NUMBER() OVER (PARTITION BY c.CustomerID ORDER BY TotalOrderAmount DESC) AS BestCustomer

FROM vwTotalAmountBilledPerOrder AS tab

INNER JOIN Orders AS o ON

o.OrderID = tab.OrderID

INNER JOIN Customers AS c ON

c.CustomerID = o.CustomerID

So my question is, is there a more efficient way to do this in SQL 2000, or should I just bite the bullet and use SQL 2005?

Thanks for any help!

---

webrunner

-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

Viewing 4 posts - 1 through 3 (of 3 total)

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

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

Try this!

-- Prepare sample data

DECLARE @Sample TABLE (Course INT, StartTime DATETIME, EndTime DATETIME)

INSERT

@Sample

SELECT 100, '3/30/07 1:00 PM', '3/30/2007 3:00 PM' UNION ALL

SELECT 100, '4/7/07 1:00 PM', '4/7/2007 3:00 PM' UNION ALL

SELECT 100, '4/14/07 1:00 PM', '4/14/2007 3:00 PM' UNION ALL

SELECT 200, '4/1/07 1:00 PM', '4/1/2007 3:00 PM' UNION ALL

SELECT 200, '4/8/07 1:00 PM', '4/8/2007 3:00 PM' UNION ALL

SELECT 200, '4/15/07 1:00 PM', '4/15/2007 3:00 PM'

-- Stage the data

DECLARE @Stage TABLE (RecID INT IDENTITY(1, 1), Course INT, StartTime DATETIME, EndTime DATETIME)

INSERT

@Stage (Course, StartTime, EndTime)

SELECT Course,

StartTime,

EndTime

FROM @Sample

ORDER BY Course,

StartTime

-- Show the expected output

SELECT s.Course,

s.RecID + 1 - x.Grp AS Session,

s.StartTime,

s.EndTime

FROM @Stage AS s

INNER JOIN (

SELECT Course,

MIN(RecID) AS Grp

FROM @Stage

GROUP BY Course

) AS x ON x.Course = s.Course

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

webrunner SSC-Dedicated Points: 31890 More actions · Answer 2

Hello,

Sorry to take so long to respond, but I wanted to thank you for this code. It worked exactly as needed! I am still a little amazed at the syntax itself - I never would have figured this out.

Thanks again!

---

webrunner

-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

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

As long as you understand what the query do, it's fine.

Thanks. And good luck!

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