August 13, 2008 at 6:48 am
I have trouble to write the select statement to reset the sequence number when value of one column changed. I don't want to use cursor if possible.
Example
Table - Course
Courseid,
Coursename
Table - ClassSchedule
ClassScheduleid
ClassSchedueTime
Courseid
INSERT INTO Course(Courseid, CourseName)
SELECT 1, 'Math101'
UNION
SELECT 2, 'Math102'
INSERT INTO ClassSchedule(ClassScheduleid, ClassScheduleTime, Courseid)
SELECT 1, '09:00am', 1
UNION
SELECT 2, '11:00am', 1
UNION
SELECT 3, '02:00pm', 1
UNION
SELECT 4, '08:00am', 2
UNION
SELECT 5, '01:00pm', 2
UNION
SELECT 6, '02:00pm', 2
UNION
SELECT 7, '03:00pm', 2
I want to result in courseid, classscheduleTime order and assign a sequence number.
Courseid, ClassSchedule, Sequence
1, '09:00AM', 1
1, '11:00AM', 2
1, '02:00PM', 3
2, '08:00AM', 1
2, '01:00PM', 2
2, '02:00PM', 3
2, '03:00PM', 4
Thanks
August 13, 2008 at 7:22 am
Loner
If you are using 2005 there is a built in function to do this:
SELECT ClassScheduleid
, ClassScheduleTime
, Courseid
, RANK() OVER (PARTITION BY Courseid ORDER BY ClassScheduleTime) AS [Rank]
FROM dbo.ClassSchedule
ORDER BY Courseid
, ClassScheduleTime
However, since this is posted in the 2000 section I'll assume that is what you are working with 😀 The following is a quick down and dirty routine which should do what you need though probably won't win any prizes for efficiency 😛
SELECT c.ClassScheduleid
, c.ClassScheduleTime
, c.Courseid
, (SELECT COUNT(*)
FROM dbo.ClassSchedule
WHERE Courseid = c.Courseid
AND ClassScheduleTime < c.ClassScheduleTime) + 1 AS [Rank]
FROM dbo.ClassSchedule c
ORDER BY c.Courseid
, c.ClassScheduleTime
August 13, 2008 at 7:57 am
It is SQL Server 2000 so I cannot use RANK, that is why I posted the question in this forum.
The one you posted for SQL Server 2000 worked liked a charm.
Thanks
August 17, 2008 at 7:00 pm
Loner (8/13/2008)
It is SQL Server 2000 so I cannot use RANK, that is why I posted the question in this forum.The one you posted for SQL Server 2000 worked liked a charm.
Thanks
That's a ticking time bomb in the face of scalability. It's known as a "Triangular Join" and you need to read about how bad they are... they're worse than a cursor and can sometimes be millions of times worse than a cursor if any decent rowcount is involved! Please read the following URL about Triangular Joins...
http://www.sqlservercentral.com/articles/T-SQL/61539/
Then, take a gander at how to do ranking in SQL Server 2000 in the following article... it takes a bit to setup properly, but it'll save your life as a developer when the rowcounts start to get large...
http://www.sqlservercentral.com/articles/Advanced+Querying/61716/
--Jeff Moden
Change is inevitable... Change for the better is not.
August 18, 2008 at 5:15 am
Jeff,
That's a ticking time bomb in the face of scalability. It's known as a "Triangular Join" and you need to read about how bad they are...
How to find whether our code comes under 'Triangular Join' ?
I have seen most of times, whenever you see any code at first sight, you are saying easily 'It is using Triangular Join'. How you are finding that ?
If you tell (teach ) how to know whether the code is using Triangular Join or not, it would be very useful for me ( your student).
karthik
August 18, 2008 at 5:35 am
Easy.
Look at the
1) JOIN arguments
2) WHERE arguments
3) Correlated subqueries arguments
Equal character "=" only are most often OK, but the character combinations to look for are mostly
>
>=
<
<=
<> or !=
Haven't you read Jeff's article yet?
There are even some small nice graphs illustrating the each and one scenario with the comparisons above.
N 56°04'39.16"
E 12°55'05.25"
August 18, 2008 at 5:15 pm
karthikeyan (8/18/2008)
Jeff,That's a ticking time bomb in the face of scalability. It's known as a "Triangular Join" and you need to read about how bad they are...
How to find whether our code comes under 'Triangular Join' ?
I have seen most of times, whenever you see any code at first sight, you are saying easily 'It is using Triangular Join'. How you are finding that ?
If you tell (teach ) how to know whether the code is using Triangular Join or not, it would be very useful for me ( your student).
Basically, Peso is correct... if it contains a correlated sub-query with an inequality and nothing else to resolve the join, it's a triangular join.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 18, 2008 at 5:16 pm
... you did read the article on Triangular Joins, didn't you? That's how you recognize them.
http://www.sqlservercentral.com/articles/T-SQL/61539/
--Jeff Moden
Change is inevitable... Change for the better is not.
August 18, 2008 at 5:36 pm
Peso (8/18/2008)
Haven't you read Jeff's article yet?There are even some small nice graphs illustrating the each and one scenario with the comparisons above.
Thanks for the plug, Peter...:)
--Jeff Moden
Change is inevitable... Change for the better is not.
August 19, 2008 at 7:36 am
You're welcome.
I forgot to mention
BETWEEN
NOT BETWEEN
as two more potential "triangular join" triggers.
N 56°04'39.16"
E 12°55'05.25"
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply