Tsql union of sub queries that each require an ORDER BY clause

Question

Hi I apologize if the answer for this is somewhere else. I looked around and I could not see a simple solution. I have a query that I would like to run. I don't care about the order of the union set, only that the inner queries return the correct data which is the "Largest Areas".

(SELECT TOP 5 * FROM [geo].[Areas] WHERE CountryID = @CountryID AND (TypeID = 'City')
ORDER BY [ShapeArea] DESC) as BiggestCities
    UNION ALL
(SELECT TOP 5 * FROM [geo].[Areas] WHERE CountryID =  @CountryID AND (TypeID = 'National park')
ORDER BY [ShapeArea] DESC) as BiggestParks

But T-sql won't let me have ORDER BY on the sub-queries, only on the whole Union... Help appreciated! I would really prefer not to have to create temporary tables or anything like that.

score 29 · Accepted Answer · edited Mar 01 '11 at 20:22

29

Try this

SELECT * 
  FROM (SELECT TOP 5 * 
          FROM [geo].[Areas] 
         WHERE CountryID = @CountryID 
           AND (TypeID = 'City')
      ORDER BY [ShapeArea] DESC) as BiggestCities
UNION ALL
SELECT * 
  FROM (SELECT TOP 5 * 
          FROM [geo].[Areas] 
         WHERE CountryID =  @CountryID 
           AND (TypeID = 'National park')
      ORDER BY [ShapeArea] DESC) as BiggestParks

edited Mar 01 '11 at 20:22

OMG Ponies

325,700
82
523
502

answered Mar 01 '11 at 20:17

PMC

4,698
3
37
57

3

Fantastic! Just what I needed. Muchas Gracias :) – Jonathon Kresner Mar 01 '11 at 20:23
de nada, It's a quick and dirty, I thought someone else would have something more elegant, haven't had time to try out @Joe Stefanelli's – PMC Mar 01 '11 at 20:29
@PaulMcCowat can you tell me how can I build this query with SQLiteQueryBuilder's buildUnionSubQuery method and buildUnionQuery Method and content providers ? – Akhil Dad Jan 29 '14 at 06:20

Joe Stefanelli · Answer 2 · 2011-03-01T20:29:08.593

2

SELECT t.* /* Excluding RowNum */
  FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY TypeID ORDER BY [ShapeArea] DESC) as RowNum
          FROM [geo].[Areas]
          WHERE CountryID = @CountryID 
            AND TypeID IN ('City', 'National park')
       ) t
  WHERE t.RowNum <= 5

edited Mar 01 '11 at 20:29

answered Mar 01 '11 at 20:23

Joe Stefanelli

132,803
19
237
235

Just what I was going to post--though since that SELECT * will return "new" column RowNum, I was putting in the old "avoid SELECT * advice. – Philip Kelley Mar 01 '11 at 20:25
@Phillip: Absolutely agree on avoiding the `SELECT *`, but it's the best I can do here without knowing the column names. Also note that I included a comment indicating the outer `SELECT` should exclude the `RowNum` column. – Joe Stefanelli Mar 01 '11 at 20:28
This is slower. I knew already, but I tested anyway - both this and Paul's answer - and confirm the general plan will be slower. – RichardTheKiwi Mar 01 '11 at 21:01

Tsql union of sub queries that each require an ORDER BY clause

2 Answers2