November 3, 2008 at 12:21 pm
I have a table that contains a course schedule. I need to select the top 12 records for each LocationID based on date.
This is what I have so far, but this only selects the top 12 records for all records:
select top 12
CourseScheduleID, Course_Number, CourseDate, LocationID
FROM
dbo.Course_Schedule
WHERE CourseDate > getdate()
ORDER BY LocationID, CourseDate
Thank you in advance for your assistance.
Norbert
November 3, 2008 at 1:11 pm
Check out the ranking functions of SQL2005.
There are a couple of good articles at CCS.
- http://www.sqlservercentral.com/articles/Advanced+Querying/61716/
- http://www.sqlservercentral.com/articles/SQL+Server+2005+-+TSQL/sequentialordering/2261/
- http://www.databasejournal.com/features/mssql/article.php/3773091
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
November 3, 2008 at 2:30 pm
ALZDBA,
Thank you for your assistance. I got it working using Rank in SQL 2005
CREATE PROCEDURE class_top12 --class_top12
AS
BEGIN
SET NOCOUNT ON;
Select
CourseScheduleID, Course_Number, CourseDate, LocationID,
RANK() OVER
(PARTITION BY LocationID
ORDER BY CourseDate DESC
) AS [Rank]
INTO #tempRankTable
FROM
dbo.Course_Schedule
WHERE CourseDate > getdate()
SELECT
*
FROM
#tempRankTable
WHERE [Rank] <=12
END
GO
November 3, 2008 at 2:44 pm
You can also skip building the temp table if you use yet another new 2005 feature (a common table expression).
No difference in perf (they actually do the same thing), though so this is a difference in syntax:
CREATE PROCEDURE class_top12 --class_top12
AS
BEGIN
SET NOCOUNT ON;
WITH RankCTE as (
Select
CourseScheduleID, Course_Number, CourseDate, LocationID,
RANK() OVER
(PARTITION BY LocationID
ORDER BY CourseDate DESC)
AS [Rank]
FROM
dbo.Course_Schedule
WHERE CourseDate > getdate())
SELECT
*
FROM RankCTE
WHERE [Rank] <=12
END
GO
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
November 3, 2008 at 2:49 pm
Matt,
Thank you very much for the information.
Norbert
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply