I have Query result but i want to show more fields

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

I have Query result but i want to show more fields

Author

Topic

mrdatabase
Starting Member

12 Posts

Posted - 2012-04-29 : 07:30:57

I have this query and it produces the result i need, however i would like to show the forename and surname field in my result, currently i only have field with a max value shown

SELECT MAX(intermediate.Total_Amount_Of_Likes) AS Total_Amount_Of_Likes
FROM (
SELECT Forename, Surname, Sum(Likes) AS Total_Amount_Of_Likes
FROM Instructors, Exercise_Class_Type, Exercise_Class_Staff, Exercise_Class_Booking, Feedback
WHERE Exercise_Class_Type.Exercise_Class_ID = Exercise_Class_Staff.Exercise_Class_ID
AND Exercise_Class_Type.Exercise_Class_ID = Exercise_Class_Booking.Exercise_Class_ID
AND Exercise_Class_Booking.Exercise_Booking_ID = Feedback.Exercise_Booking_ID
AND Instructors.Instructor_ID = Exercise_Class_Staff.Instructor_ID
AND Start_Date_Time >= Date() - 30
GROUP BY Forename, Surname
) intermediate;

Any thoughts?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-04-29 : 07:47:30

Is Date() a UDF you have defined? Don't think that is a built-in SQL Server function. If you are looking for today's date, you can use getdate() (which would also include the time part)

Regardless, you can use ORDER BY instead of using the MAX function:

SELECT TOP 1 Forename, Surname, Total_Amount_Of_Likes
FROM(
SELECT Forename, Surname, Sum(Likes) AS Total_Amount_Of_Likes
FROM Instructors, Exercise_Class_Type, Exercise_Class_Staff, Exercise_Class_Booking, Feedback
WHERE Exercise_Class_Type.Exercise_Class_ID = Exercise_Class_Staff.Exercise_Class_ID
AND Exercise_Class_Type.Exercise_Class_ID = Exercise_Class_Booking.Exercise_Class_ID 
AND Exercise_Class_Booking.Exercise_Booking_ID = Feedback.Exercise_Booking_ID
AND Instructors.Instructor_ID = Exercise_Class_Staff.Instructor_ID
AND Start_Date_Time >= Date() - 30
GROUP BY Forename, Surname
) intermediate
ORDER BY Total_Amount_Of_Likes DESC;

mrdatabase
Starting Member

12 Posts

Posted - 2012-04-29 : 07:53:37

That works perfectly. Thankyou very much :)!

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-04-29 : 08:11:19

You are quite welcome.

If there happens to be more than one row that has the highest amount of Total_Amount_Of_Likes, what do you want to do? The query above will just randomly pick one of those and display.

If you want to pick the top 1 based on the Surname or Forename, you can add those to the order by clause.

Alternatively, instead of TOP 1, you can use TOP 1 WITH TIES to get all those at the top.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources