I have the following table structure
The goal is to get the last 3 oldest records (oldest date) sorted by Name. I'm currently using subselect:
Select * from
(Select top 3 from TB
order by Data desc) A
order by Nome
Are there any other better ways to do this?
As the bfavaretto commented you should already be using the best option for your requirements.
Note you want:
For the first requirement you should use the top 3 and a sort by date to return the records that you want. With all three records in hand you sort them out just for visual issues.
Using a self join or a table variable can do this but I doubt it is efficient (but an acid test is needed to be sure).
Pedro Laini's answer does not meet the second requirement because the ordering by date will take precedence over the result presented.
Guilherme Torres's answer is not and may fail if there are "gaps" between the Ids (caused by deletion for example). In addition, in his question no correlation was mentioned between the date and the Id.
The only other reasonable option (depending on context) would be to sort the three records returned in the UI or Service.
You can do this too:
SELECT TOP 3 FROM TB ORDER BY Data DESC, Nome ASC
Is the ID sequential and the date rising? If it is, it would take the last id - 3.
Select * from TB where id >= (Select max(id) from TB) - 3 order by Nome