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.
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...
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.