Order by query/aggregate functions

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

SQL Server 2008 Forums

Transact-SQL (2008)

Order by query/aggregate functions

Author

Topic

Frosty615
Starting Member

13 Posts

Posted - 2012-01-16 : 05:57:59

Hi All,
I Have a query
[Code]
ALTER PROCEDURE [dbo].[sp_GetPersonalFlyingHoursLogBook] (@StaffID AS INT, @StartDate AS DATE, @EndDate AS DATE) AS
SELECT AircraftNumber, Duty, ClaimedBy,
DutyDate, TraineeName, pd.Surname, pd.[First], pd1.Surname AS Surname1, pd1.[First] AS First1,
CommanderName, TraineeName,
SUM(DATEDIFF(SECOND,Takeoff,Land))/3600 AS DurationHours,
(SUM(DATEDIFF(SECOND,TakeOff,Land))% 3600)/60 AS DurationMinutes,
COUNT(DUTY) AS NumberOfFlights
FROM eLogSheets els
LEFT OUTER JOIN
PersonalDetails pd
ON
pd.PDStaffID = els.AircraftCommander
LEFT OUTER JOIN
PersonalDetails pd1
ON
pd1.PDStaffID = els.Trainee
WHERE (pd.PDStaffID = @StaffID OR pd1.PDStaffID = @StaffID) AND DutyDate BETWEEN @StartDate AND @EndDate
GROUP BY
AircraftNumber, Duty, ClaimedBy,
DutyDate, TraineeName, pd.Surname, pd.[First], pd1.Surname, pd1.[First],
CommanderName, TraineeName
ORDER BY Dutydate
[/Code]

I need the resulting set of data sorted by the Column TakeOff which is held in the elogSheets table.

Problem is I cannot have TakeOff in the select list or in the GROUP BY clause and it really needs to be ordered by this column.

Is there anyway I can get around this?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-01-16 : 06:50:11

Please think about what GROUP BY means and then read your own post.

No, you're never too old to Yak'n'Roll if you're too young to die.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-16 : 06:50:52

didnt understand the significance of including TakeOff in orderby if you're not grouping by it. there will be obviously more than one value of TakeOff in each group so in such case how do you determine which value needs to be used for sorting?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Frosty615
Starting Member

13 Posts

Posted - 2012-01-16 : 07:51:36

Ok, perhaps I should explain a little more about the results that are returned.....
As an example
If I don't fly for 31 days then I have to do a check flight with one of our instructors to ensure I am safe enough to fly.
I do this and then fly with others and for these purposes this procedure is returning the correct results
However when I go to enter these into my flying log book (for which we do not need the take off/landing times but can be grouped but all the bits in the group by clause) the result set may not be in the correct order, so the initial check flight doesn't appear in the list as the first result.

I can't include the TakeOff time in current procedure as the results that are returned do not then add the duration or the number of flights i.e they will just return each individual flight.

I hope this helps?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-01-16 : 08:10:02

As Fred and Visakh pointed out, all the columns have to be either listed in the group by clause, or has to be within an aggregate function. I didn't quite understand the business logic you are trying to implement, but would ordering by the min value of TakeOff help? I am thinking this:

ORDER BY
   MIN(els.TakeOff ),
   Dutydate

Or may be it is the MAX(els.TakeOff) that you need to sort by?

Frosty615
Starting Member

13 Posts

Posted - 2012-01-16 : 08:22:36

PERFECT! That seems to have worked as I wanted it too, will test on a copy of our live data and hopefully that will return the results as I'd like them.

Simple, but had me confused!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-16 : 10:07:31

Hopefully that works for you!
If not, please post typical data and show us how TakeOff values exists for a group and then explain how you want sort using it

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Frosty615
Starting Member

13 Posts

Posted - 2012-01-16 : 11:00:00

I've had a look and it seems to be working just fine.
If it's not working as it should I will post again but that may take a couple of weeks to get some proper live data flowing through the system.

Thanks to all for your help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-16 : 11:03:16

no probs
you're welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources