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 BY
is 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
)?
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.