November 7, 2008 at 9:19 am
I am trying to do a count on my Lesson CatId to find Instructors that have the highest number of bookings keeping in mind that there may be more that one instructor with the highest bookings. If 3 instructors have the same number of bookings which the value is the highest then I need to show all 3 instructors only as having the highest number of bookings.
My results is coming up with the highest bookings in Descending order but it is showing all the instructors calculating the highest bookings for each. There are only 2 instructor with the highest bookings that I need to show. How can I extract the 2 that has the highest bookings?
Here is my code:
SELECT MAX([Booking].[Staff_Id]) AS Staff_Id, MAX([Staff].[First_Name]) AS First_Name, MAX([Staff].[Last_Name]) AS Last_Name, Count([Booking].[Lesson_CatId]) AS Highest_Booking
FROM Staff INNER JOIN Booking ON [Staff].[Staff_Id]=[Booking].[Staff_Id]
WHERE ((([Staff].[Is_Instructor])<>False))
GROUP BY [Booking].[Staff_Id], [Staff].[First_Name], [Staff].[Last_Name], [Staff].[Is_Instructor]
ORDER BY COUNT([Booking].[Lesson_CatId])DESC;
November 7, 2008 at 9:33 am
Instead of using MAX, look into using TOP 1 with TIES.
SELECT Top 1 WITH TIES
[Booking].[Staff_Id] ,
[Staff].[First_Name],
[Staff].[Last_Name],
Count([Booking].[Lesson_CatId]) AS Highest_Booking
FROM Staff INNER JOIN Booking ON [Staff].[Staff_Id]=[Booking].[Staff_Id]
WHERE ((([Staff].[Is_Instructor])<>False))
GROUP BY [Booking].[Staff_Id], [Staff].[First_Name], [Staff].[Last_Name], [Staff].[Is_Instructor]
ORDER BY COUNT([Booking].[Lesson_CatId]) DESC;
----------------------------------------------------------------------------------
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 7, 2008 at 9:55 am
Thank you so much.
This code worked perfectly.
SELECT Top 1
[Booking].[Staff_Id] ,
[Staff].[First_Name],
[Staff].[Last_Name],
Count([Booking].[Lesson_CatId]) AS Highest_Booking
FROM Staff INNER JOIN Booking ON [Staff].[Staff_Id]=[Booking].[Staff_Id]
WHERE ((([Staff].[Is_Instructor])<>False))
GROUP BY [Booking].[Staff_Id], [Staff].[First_Name], [Staff].[Last_Name], [Staff].[Is_Instructor]
ORDER BY COUNT([Booking].[Lesson_CatId]) DESC;
November 7, 2008 at 10:15 am
If you don't add in the WITH TIES, you will only get one record (even if three trainers have the same highest count).
----------------------------------------------------------------------------------
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?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply