Select Top With Multiple Union And With Order By

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 D

Solution 1:

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 (partitionbyCASEWHEN Tipo LIKE'%OS%'then'OS'WHEN Tipo like'%Rad%'then'Rad'WHEN Tipo LIKE'%Aci%'then'ACI'WHEN tipo LIKE'%Out%'then'OUT'endorderby 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'%'ORDERBY DATA desc) A 
  UNIONALLSELECT*FROM  
  (SELECT TOP(10) *from dbo.Eventos WHERE Tipo LIKE'%Rad%'AND Distrito LIKE'%'ORDERBY DATA DESC) B
  UNIONALLSELECT*FROM 
  (SELECT TOP(10) *from dbo.Eventos WHERE Tipo LIKE'%Aci%'AND Distrito LIKE'%'ORDERBY DATA DESC) C
  UNIONALLSELECT*FROM 
  (SELECT TOP(10) *from dbo.Eventos WHERE Tipo LIKE'%Out%'AND Distrito LIKE'%'ORDERBY Data DESC) D
) E
ORDERBY 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.