Sorting data according to MAX() value

SSAS and SSRS
1

Hello , i have a problem involving MAX(). I have a table with a series of columns. Now lets say i have two columns , one named Sleeping_Time and the other Start_Time, the table is call Gross_Sleep_Timetable. I want to sort the Start Time using the Maximum value of Sleeping time. So i want to get the Start Time based in the row where the Max() value of Sleeping Time is located. How would i do so? I also want the values to change according to dynamic filters i have to General Timetable. This means i can select different MAX() values of Sleeping Time in a list, and get different values of Start Time in a list. I tried implementing a nested loop in my Select function, but it returned multiple values.

Select

(Select Start_Time

From Gross_Sleep_Timetable gs

where gs.Sleeping_Time=(Select Max(gst.Sleeping_Time))

)

From

Gross_Sleep_Timetable gst

2 
Select	Start_Time, ... AnyOtherColumns ...
From	Gross_Sleep_Timetable AS GS
where	gs.Sleeping_Time =
	(
		Select	MAX(gst.Sleeping_Time)
		FROM	Gross_Sleep_Timetable AS GST
	)

If there is more than one row that exactly matches MAX(gst.Sleeping_Time) then the results will display all such rows. If you don't want that then use SELECT TOP 1 and be sure to add an ORDER BY to get the record that is most appropriate.

3

Would it be possible to key the condition to only one select clause? For eg, could i make it in a way that only Start_Time changes, like only Start_Time get sort out by Max(gst.Sleeping_Time)? I do not want the whole query to be affected by the clause. For instance, in the Where Clause , gs.Start_Time =(select start_time where Sleeping_Time=Max(Sleeping_Time)). I understand tho this is impossible, as i cannot have an aggregate in the where clause.

4 
Select	TOP 1 Start_Time, ... AnyOtherColumns ...
From	Gross_Sleep_Timetable AS GS
ORDER BY gs.Sleeping_Time DESC, AnyOtherTieBreakColumns
1 Like
5

Thanks for your help !