Combining Order by and Union in SQL Server

Combining ORDER BY AND UNION in SQL Server

Put your order by and top statements into sub-queries:

select first.Id, first.Name 
from (
    select top 1 * 
    from Locations 
    order by Id) first
union all
select last.Id, last.Name 
from (
    select top 1 * 
    from Locations 
    order by Id desc) last

How to use order by with union all in sql?

SELECT  * 
FROM 
        (
            SELECT * FROM TABLE_A 
            UNION ALL 
            SELECT * FROM TABLE_B
        ) dum
-- ORDER BY .....

but if you want to have all records from Table_A on the top of the result list, the you can add user define value which you can use for ordering,

SELECT  * 
FROM 
        (
            SELECT *, 1 sortby FROM TABLE_A 
            UNION ALL 
            SELECT *, 2 sortby FROM TABLE_B
        ) dum
ORDER   BY sortby

Order by clause with Union in Sql Server

You need to use a sub-query with CASE in ORDER BY clause like this:

SELECT * FROM
(
  Select 0 PartyId, 'All' Name
  Union
  select PartyId, Name
  from PartyMst
) tbl
ORDER BY CASE WHEN PartyId = 0 THEN 0 ELSE 1 END
,Name

Output:

PARTYID	NAME
0	All
2	AAKASH & CO.
3	SHAH & CO.
1	SHIV ELECTRONICS

Using group/order by with union clause in sql query

A union query may only have one order by clause.

If you are satisfied with ordering the whole resultset, you can remove all order by clauses and just keep the very last one, at the end of the query. It applies to the entire dataset that union generates.

Note that your UNIONs are equivalent to UNION ALLs - because the client name is different in each member - and should be phrased as such.

If, on the other hand, you want to order reach sub-result, then this is different. Basically you need a flag in each member, that can then be used to identify each group. The client name might be a good pick, so:

order by client_name, client_id

SQL - How to Order By in UNION query

Put a UNION ALL in a derived table. To keep duplicate elimination, do select distinct and also add a NOT EXISTS to second select to avoid returning same person twice if found in both tables:

select name, surname
from
(
    select distinct name, surname, 1 as tno
    from table1
    union all
    select distinct name, surname, 2 as tno
    from table2 t2
    where not exists (select * from table1 t1
                      where t2.name = t1.name
                        and t2.surname = t1.surname)
) dt
order by tno, surname, name

SELECT TOP with multiple UNION and with ORDER BY

If I understand correctly, you want the 10 most recent eventos for each tipo like os, rad, aci, out. You determine the most recent by looking at data (I'm assuming that's a date field) We can accomplish this by using a ROW_NUMBER partition by the tipo without all the unions. but since we have wild cards for tipo, we need to define them into the same set; which can be done with a case statement within the window function.

I'm assuming that Distrito would have the same value for each tipo when populated.

WITH CTE AS (
    SELECT E.*, Row_number() over (partition by 
      CASE WHEN Tipo LIKE '%OS%' then 'OS' 
           WHEN Tipo like '%Rad%' then 'Rad'
           WHEN Tipo LIKE '%Aci%' then 'ACI'
           WHEN tipo LIKE '%Out%' then 'OUT' end order by data Desc) RN
    FROM dbo.Eventos E 
    WHERE (Tipo LIKE '%OS%' OR Tipo LIKE '%Rad%' OR Tipo LIKE '%Aci%' OR Tipo LIKE '%Out%') 
      AND Distrito like '%')
SELECT * 
FROM  cte 
WHERE RN <=10;

We use the Common table Expression (CTE) because we need the results to be generated for the rownumbers before we can limit by them. Since row_number will restart for every different tipo, we simply need to get those <=10 to replace your top.

Or to just build on what you've done...

SELECT * FROM (
  SELECT * FROM 
  (SELECT TOP(10) * from dbo.Eventos WHERE Tipo LIKE '%OS%' AND Distrito LIKE '%' ORDER BY DATA desc) A 
  UNION ALL
  SELECT * FROM  
  (SELECT TOP(10) * from dbo.Eventos WHERE Tipo LIKE '%Rad%' AND Distrito LIKE '%' ORDER BY DATA DESC) B
  UNION ALL 
  SELECT * FROM 
  (SELECT TOP(10) * from dbo.Eventos WHERE Tipo LIKE '%Aci%' AND Distrito LIKE '%' ORDER BY DATA DESC) C
  UNION ALL 
  SELECT * FROM 
  (SELECT TOP(10) * from dbo.Eventos WHERE Tipo LIKE '%Out%' AND Distrito LIKE '%' ORDER BY Data DESC) D
) E
ORDER BY DATA DESC;

You need the subqueries to have their own order by to get the right top 10 for each tipo grouping. To accomplish this you need each query to act as a inline view and fully materialize (actually generate the data) before the union occurs.

SELECT TOP N with UNION and ORDER BY

A Union query works thus: execute the queries, then apply the order by clause. So with

SELECT TOP 5 [ID], [Description], [Inactive]
FROM #T1
UNION ALL
SELECT TOP 5 [ID], [Description], [Inactive]
FROM #T2
ORDER BY [Inactive], [Description];

you select five arbitrarily chosen records from #T1 plus five arbitrarily chosen records from #T2 and then you order these. So you need subqueries or with clauses. E.g.:

SELECT * FROM
(
  (
    SELECT TOP 5 [ID], [Description], [Inactive]
    FROM #T1
    ORDER BY [Inactive], [Description]
  )
  UNION ALL
  (
    SELECT TOP 5 [ID], [Description], [Inactive]
    FROM #T2
    ORDER BY [Inactive], [Description]
  )
) t;

So your workaround is not a workaround at all, but the proper query.

Using different order by with union

This should work:

SELECT * 
FROM (SELECT TOP 10 A.*, 0 AS Ordinal
      FROM A
      ORDER BY [Price]) AS A1

UNION ALL

SELECT * 
FROM (SELECT TOP 3 A.*, 1 AS Ordinal
      FROM A
      ORDER BY [Name]) AS A2

ORDER BY Ordinal

From MSDN:

In a query that uses UNION, EXCEPT, or INTERSECT operators, ORDER BY
is allowed only at the end of the statement. This restriction applies
only to when you specify UNION, EXCEPT and INTERSECT in a top-level
query and not in a subquery.

Edited: to force the order you need to apply an ORDER BY to the outer query. I've added a constant value column to both queries.

Combining Order by and Union in SQL Server