"Order by" in a sub query

Question

I am trying to select the student that has most hours of Kindness of each grade (9-12). I wrote this code (in sql server 2012) to get the student with most hours:

(select top 1  stu_first, stu_last, sum(KIND_hours) as total from STUDENT inner join KIND
on student.stu_id=KIND.stu_id
where (12-(STU_CLASS_OF-2014))=9
group by stu_first, stu_last
order by total desc)

This code works, but when I try to union the code together for just two grades I get this error: Incorrect syntax near the keyword 'order'. My code is here

(select top 1  stu_first, stu_last, sum(KIND_hours) as total from STUDENT inner join KIND
on student.stu_id=KIND.stu_id
where (12-(STU_CLASS_OF-2014))=9
group by stu_first, stu_last
order by total desc)
union
(select top 1  stu_first, stu_last, sum(KIND_hours) as total from STUDENT inner join KIND
on student.stu_id=KIND.stu_id
where (12-(STU_CLASS_OF-2014))=10
group by stu_first, stu_last
order by total desc)

stu_class_of is the year that the student would graduate

Answer 1

Your UNION shouldn't be 2 subselects. You need to remove 1 of the ORDER BYs:

DECLARE @T1 TABLE (Id INT);
INSERT INTO @T1 VALUES (1), (2), (3);

DECLARE @T2 TABLE (Id INT);
INSERT INTO @T1 VALUES (7), (9), (-1);


SELECT * FROM @T1
UNION
SELECT * FROM @T2
ORDER BY Id DESC;


/* Output:
9 --From @T2
7 --From @T2
3
2
1
-1 --From @T2
*/

Edit: I realize now what it is that you actually want. I should have realized by seeing your TOP 1's combined with a UNION. What you're actually looking for is called a CTE (Common Table Expression). Here's the query you're looking for:

/*
SET IDENTITY_INSERT [STUDENT] ON;
INSERT INTO [STUDENT] (stu_id, stu_first, stu_last, STU_CLASS_OF) VALUES
(1,  'Leonard', 'Hofstadter', 1989),
(2, 'Sheldon', 'Cooper', 1989),
(3, 'Howard', 'Wolowitz', 1989),
(4, 'Raj', 'Koothrappali', 1989),
(5, 'Penny', '?', 2001),
(6, 'Bernadette', 'Rostenkowski ', 2001),
(7, 'Amy', 'Fowler', 2001)
SET IDENTITY_INSERT [STUDENT] OFF;

SET IDENTITY_INSERT [KIND] ON;
INSERT INTO [KIND] (stu_id, [hours]) VALUES
(1,  10),
(2, 13),
(3, 7),
(4, 54),
(5, 78),
(6, 13),
(7, 64)
SET IDENTITY_INSERT [KIND] OFF;
*/

WITH CTE AS (
    SELECT ROW_NUMBER() OVER (PARTITION BY [STU_CLASS_OF] ORDER BY SUM([KIND].[hours]) DESC) AS [RowNumber],
        [stu_first],
        [stu_last],
        SUM([KIND].[hours]) AS [total]
    FROM [STUDENT]
        INNER JOIN [KIND] ON [STUDENT].[stu_id] = [KIND].[stu_id]
    GROUP BY [stu_first], [stu_last], [STU_CLASS_OF]
)
SELECT
    [stu_first],
    [stu_last],
    [total]
FROM CTE
WHERE RowNumber = 1
ORDER BY [STU_CLASS_OF] DESC;

Here's a short explination of what's actually going on behind the scenes: A CTE is like a subselect, but it has a lot of awesome features. One of them is ROW_NUMBER() which gives an ID each time it sees whatever you write in the PARTION BY {something}.

But if it did that randomly, you'd get different results each time. So you also add an ORDER BY to it, so that it knows which ones should get the lowest ID. In this case, we're only interrested in 1 student from each class. So I put in [STU_CLASS_OF]. But you also only want the TOP one from each. So I order it by [total] DESC

If you then wanted e.g. TOP 3 from each class instead of TOP 1, then you simply change the WHERE RowNumber = 1 to be 3 instead.

If you only want the data for some specific classes and not everyone like I did, then you simply add your previous WHERE clause to the final SELECT like this:

WHERE RowNumber = 1
    AND (12 - ([STU_CLASS_OF] - 2014)) = 9
    AND (12 - ([STU_CLASS_OF] - 2014)) = 10

Answer 2

When you use UNION you cannot use ORDER BY in the sub-queries. I'm sure there is a better solution, but wrapping your sub-queries in another level will work for you. Try this:

SELECT * FROM
(SELECT TOP 1 stu_first, stu_last, SUM(kind_hours) AS total 
 FROM student
 INNER JOIN kind ON student.stu_id = kind.stu_id
 WHERE (12-(stu_class_of-2014)) = 9
 GROUP BY stu_first, stu_last
 ORDER BY total DESC)

UNION

SELECT * FROM
(SELECT TOP 1 stu_first, stu_last, SUM(kind_hours) AS total 
 FROM student
 INNER JOIN kind ON student.stu_id = kind.stu_id
 WHERE (12-(stu_class_of-2014)) = 10
 GROUP BY stu_first, stu_last
 ORDER BY total DESC)

Answer 3

Try this:

Make your 2 queries.."derived" Tables. Here is a working Northwind example:

  Use Northwind
     GO

Select  OrderID , CustomerID , EmployeeID from 
( Select TOP 1 OrderID , CustomerID , EmployeeID from  dbo.Orders where ShipCountry='France' Order by ShippedDate )
as derived1
UNION ALL
Select  OrderID , CustomerID , EmployeeID from 
( Select TOP 1 OrderID , CustomerID , EmployeeID from  dbo.Orders where ShipCountry='Germany' Order by ShippedDate  )
as derived2

Here it is with your queries plugged in , but I cannot test them since I don't have your DDL.

    Select  * from 
    ( select top 1  stu_first, stu_last, sum(KIND_hours) as total from STUDENT inner join KIND
on student.stu_id=KIND.stu_id
where (12-(STU_CLASS_OF-2014))=9
group by stu_first, stu_last
order by total desc )
    as derived1
    UNION ALL
    Select  * from 
    ( select top 1  stu_first, stu_last, sum(KIND_hours) as total from STUDENT inner join KIND
on student.stu_id=KIND.stu_id
where (12-(STU_CLASS_OF-2014))=10
group by stu_first, stu_last
order by total desc  )
    as derived2

OLD ANSWER FOR POSSIBLE WORK AROUND

If you need "Order by" distinction between the two sets of data, you can use this trick:

IF OBJECT_ID('tempdb..#TableOne') IS NOT NULL
begin
        drop table #TableOne
end



IF OBJECT_ID('tempdb..#TableTwo') IS NOT NULL
begin
        drop table #TableTwo
end



CREATE TABLE #TableOne
( 
SurrogateKeyIDENTITY int not null IDENTITY (1,1) , 
NameOfOne varchar(12)
)



CREATE TABLE #TableTwo
( 
SurrogateKeyIDENTITY int not null IDENTITY (1,1) , 
NameOfTwo varchar(12)
)


Insert into #TableOne (NameOfOne)
Select 'C' as Alpha UNION ALL Select 'B' as Alpha UNION ALL Select 'D' as Alpha UNION ALL Select 'Z' as Alpha


Insert into #TableTwo (NameOfTwo)
Select 'T' as Alpha UNION ALL Select 'W' as Alpha UNION ALL Select 'X' as Alpha UNION ALL Select 'A' as Alpha



select 1 , NameOfOne from #TableOne
UNION
select 2 , NameOfTwo from #TableTwo
Order by 1 , 2 /* These are the "Ordinal Positions of the Column*/




IF OBJECT_ID('tempdb..#TableOne') IS NOT NULL
begin
        drop table #TableOne
end


IF OBJECT_ID('tempdb..#TableTwo') IS NOT NULL
begin
        drop table #TableTwo
end

Answer 4

Every time I want to order a sub-query I tend it to wrap in an outer query like this:

select * from 
(select * from xx order by x) x

This way yo can embed this query in a UNION or any other situation and it will always work since you apply the sort in the inner query.