Order by in sub select in a Derived Table

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 in sub select in a Derived Table

Author

Topic

Frosty615
Starting Member

13 Posts

Posted - 2012-04-25 : 05:39:08

Hi all,
As you can tell, I want to do something within SQL Server that isn't possible!
So, I currently have two tables that I want to query using a UNION ALL.
That isn't a problem.
However the resulting records are not sorted in the correct order.
Of course, you're going to say 'put the Order By' in the outer select...
Problem is the fields do not exist in the outer select and cannot exist in the outer select as it would defeat the object of what I am trying to do.

The query I am trying to write is:


ALTER PROCEDURE sp_GETErroneous (@Staffid INT, @StartDate DATE, @EndDate DATE) AS
SELECT * FROM
(SELECT AircraftNumber, Duty, ClaimedBy, els.dutydate, TraineeName, pd.Surname, pd.[First], pd1.Surname AS Surname1, pd1.[First] AS First1,
CommanderName, SUM(DATEDIFF(SECOND,Takeoff,Land))/3600 AS DurationHours, (SUM(DATEDIFF(SECOND,TakeOff,Land))% 3600)/60 AS DurationMinutes,
COUNT(DUTY) AS NumberOfFlights, pd.PDStaffID AS PDStaffidCapt, pd1.PDStaffID AS PDStaffidTrainee, GICCadetID,
GIC.FirstName AS GICFirstName, GIC.Surname AS GICSurname
FROM eLogSheets els
LEFT OUTER JOIN
PersonalDetails pd
ON
pd.PDStaffID = els.AircraftCommander
LEFT OUTER JOIN
PersonalDetails pd1
ON
pd1.PDStaffID = els.Trainee
LEFT OUTER JOIN
GICAllocations GIC
ON
els.GICCadetID = GIC.IndexNumber
WHERE (pd.PDStaffID = @StaffID OR pd1.PDStaffID = @StaffID) AND els.dutydate BETWEEN @StartDate AND @EndDate
GROUP BY
els.DutyDate, pd.Surname, pd.[First], pd1.Surname, pd1.[First], AircraftNumber, Duty, GICCadetID, ClaimedBy, 
CommanderName, TraineeName, pd.PDStaffID, pd1.PDStaffID, pd.LaunchesBroughtForwards, pd1.LaunchesBroughtForwards,
pd.HoursBroughtForwards, pd1.HoursBroughtForwards, GIC.FirstName, GIC.Surname
--ORDER BY DutyDate, MIN(TakeOff)<----This is the problem

UNION ALL

SELECT AircraftNumber, Duty, ClaimedBy, 
els.dutydate, TraineeName, pd.Surname, pd.[First], pd1.Surname AS Surname1, pd1.[First] AS First1,
CommanderName, DATEPART(HOUR, Duration) AS DurationHours,
DATEPART(MINUTE,Duration) AS DurationMinutes,
SUM(NumberOfFlights) AS NumberOfFlights, pd.PDStaffID AS PDStaffidCapt, pd1.PDStaffID AS PDStaffidTrainee, GICCadetID,
GIC.FirstName AS GICFirstName, GIC.Surname AS GICSurname
FROM ErroneousFlightData els
LEFT OUTER JOIN
PersonalDetails pd
ON
pd.PDStaffID = els.AircraftCommander
LEFT OUTER JOIN
PersonalDetails pd1
ON
pd1.PDStaffID = els.Trainee
LEFT OUTER JOIN
GICAllocations GIC
ON
els.GICCadetID = GIC.IndexNumber
WHERE (pd.PDStaffID = @StaffID OR pd1.PDStaffID = @StaffID) AND els.dutydate BETWEEN @StartDate AND @EndDate
GROUP BY
els.DutyDate, pd.Surname, pd.[First], pd1.Surname, pd1.[First], AircraftNumber, Duty,GICCadetID,  ClaimedBy, 
CommanderName, TraineeName, pd.PDStaffID, pd1.PDStaffID, pd.LaunchesBroughtForwards, pd1.LaunchesBroughtForwards,
pd.HoursBroughtForwards, pd1.HoursBroughtForwards, GIC.FirstName, GIC.Surname, Duration, TakeOff, Land
)
AS Mydt
GROUP BY
DutyDate, AircraftNumber, Duty, Surname, [First], Surname1, First1, ClaimedBy, 
CommanderName, TraineeName, PDStaffidCapt, PDStaffidTrainee, DurationHours, DurationMinutes, NumberOfFlights,
GICCadetID, GICFirstName, GICSurname
ORDER BY DutyDate

Is there another way of doing what I am trying to achieve?
A 'linked' item that was resolved can be found here :[url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=170244[/url]

Thanks in advance

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2012-04-25 : 05:46:07

Use ROW_NUMBER() to do your sorting for you.

http://msdn.microsoft.com/en-us/library/ms186734.aspx

Frosty615
Starting Member

13 Posts

Posted - 2012-04-25 : 06:09:08

RickD,
Excellent, I have written this in to my Procedure and it seems to be working, though I will need to do a lot more testing on it...
Procedure now reads:


ALTER PROCEDURE sp_GETErroneous (@Staffid INT, @StartDate DATE, @EndDate DATE) AS
SELECT  * FROM
(SELECT ROW_NUMBER() OVER(ORDER BY MIN(TakeOff)) AS Row, AircraftNumber, Duty, ClaimedBy, els.dutydate, TraineeName, pd.Surname, pd.[First], pd1.Surname AS Surname1, pd1.[First] AS First1,
CommanderName, SUM(DATEDIFF(SECOND,Takeoff,Land))/3600 AS DurationHours, (SUM(DATEDIFF(SECOND,TakeOff,Land))% 3600)/60 AS DurationMinutes,
COUNT(DUTY) AS NumberOfFlights, pd.PDStaffID AS PDStaffidCapt, pd1.PDStaffID AS PDStaffidTrainee, GICCadetID,
GIC.FirstName AS GICFirstName, GIC.Surname AS GICSurname
FROM eLogSheets els
LEFT OUTER JOIN
PersonalDetails pd
ON
pd.PDStaffID = els.AircraftCommander
LEFT OUTER JOIN
PersonalDetails pd1
ON
pd1.PDStaffID = els.Trainee
LEFT OUTER JOIN
GICAllocations GIC
ON
els.GICCadetID = GIC.IndexNumber
WHERE (pd.PDStaffID = @StaffID OR pd1.PDStaffID = @StaffID) AND els.dutydate BETWEEN @StartDate AND @EndDate
GROUP BY
els.DutyDate, pd.Surname, pd.[First], pd1.Surname, pd1.[First], AircraftNumber, Duty, GICCadetID, ClaimedBy, 
CommanderName, TraineeName, pd.PDStaffID, pd1.PDStaffID, pd.LaunchesBroughtForwards, pd1.LaunchesBroughtForwards,
pd.HoursBroughtForwards, pd1.HoursBroughtForwards, GIC.FirstName, GIC.Surname

UNION ALL

SELECT ROW_NUMBER() OVER(ORDER BY els.DutyDate) AS Row, AircraftNumber, Duty, ClaimedBy, 
els.dutydate, TraineeName, pd.Surname, pd.[First], pd1.Surname AS Surname1, pd1.[First] AS First1,
CommanderName, DATEPART(HOUR, Duration) AS DurationHours,
DATEPART(MINUTE,Duration) AS DurationMinutes,
SUM(NumberOfFlights) AS NumberOfFlights, pd.PDStaffID AS PDStaffidCapt, pd1.PDStaffID AS PDStaffidTrainee, GICCadetID,
GIC.FirstName AS GICFirstName, GIC.Surname AS GICSurname
FROM ErroneousFlightData els
LEFT OUTER JOIN
PersonalDetails pd
ON
pd.PDStaffID = els.AircraftCommander
LEFT OUTER JOIN
PersonalDetails pd1
ON
pd1.PDStaffID = els.Trainee
LEFT OUTER JOIN
GICAllocations GIC
ON
els.GICCadetID = GIC.IndexNumber
WHERE (pd.PDStaffID = @StaffID OR pd1.PDStaffID = @StaffID) AND els.dutydate BETWEEN @StartDate AND @EndDate
GROUP BY
els.DutyDate, pd.Surname, pd.[First], pd1.Surname, pd1.[First], AircraftNumber, Duty,GICCadetID,  ClaimedBy, 
CommanderName, TraineeName, pd.PDStaffID, pd1.PDStaffID, pd.LaunchesBroughtForwards, pd1.LaunchesBroughtForwards,
pd.HoursBroughtForwards, pd1.HoursBroughtForwards, GIC.FirstName, GIC.Surname, Duration
)
AS Mydt
GROUP BY
DutyDate, AircraftNumber, Duty, Surname, [First], Surname1, First1, ClaimedBy, 
CommanderName, TraineeName, PDStaffidCapt, PDStaffidTrainee, DurationHours, DurationMinutes, NumberOfFlights,
GICCadetID, GICFirstName, GICSurname, Row
ORDER BY dutydate, row

Does this look right? It seems to be returning the correct results so far

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2012-04-26 : 04:15:22

Yeah, looks right to me.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources