SELECT TOP with multiple UNION and with ORDER BY

Question

I want to select the latest records from the DB in SQL Server. If only one item is selected the final output is this:

SELECT TOP(10) * from dbo.Eventos WHERE Tipo LIKE '%OS%' AND Distrito LIKE '%'

+ always added at the end:

ORDER BY Data DESC

NOTE: Distrito LIKE '%' must stay as it sometimes is programatically changed to something other than %.

If there are more items selected, the query gets one UNION line added programatically for each item. At the end, the ORDER BYis added as always. Example with all 4 items checked:

SELECT TOP(10) * from dbo.Eventos WHERE Tipo LIKE '%OS%' AND Distrito LIKE '%' 
UNION ALL SELECT TOP(10) * from dbo.Eventos WHERE Tipo LIKE '%Rad%' AND Distrito LIKE '%' 
UNION ALL SELECT TOP(10) * from dbo.Eventos WHERE Tipo LIKE '%Aci%' AND Distrito LIKE '%'
UNION ALL SELECT TOP(10) * from dbo.Eventos WHERE Tipo LIKE '%Out%' AND Distrito LIKE '%' 
ORDER BY Data DESC

But this gives me the OLDEST 10 results for each WHERE clause sorted BY Data DESC.

How can i get the NEWEST X results for each item (WHERE)?

This really looks like SQL server not mySQL. Are you sure your tag is correct? — xQbert, Dec 23 '16 at 14:52

xQbert · Accepted Answer · 2016-12-23T15:41:52.670

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.

Your first solution gives me `Incorrect syntax near 'Row_number'`. Your second solution also don't work (3 `Incorrect syntax near the keyword 'ORDER'` errors, Lines 5, 7 and 9), but seems more what i need (don't forget additional SELECT (UNION) are added programatically. I've updated my question for clarification. — Dillinger, Dec 23 '16 at 15:25
1st one was missing a comma between E.* and row_number. 2nd one needed multiple selects added and some ()'s. With all the repetition in the 2nd solution, you can see why I find the 1st more elegant. — xQbert, Dec 23 '16 at 15:44
The 1st one is less simple to implement programatically than the 2nd solution, that worked exactly as i wanted. — Dillinger, Dec 23 '16 at 16:13

SELECT TOP with multiple UNION and with ORDER BY

1 Answers1

Linked