Select from the last records sorted by 1 field

Question

I have the following table structure

The goal is to get the last 3 oldest records (oldest date) ordered by Name. Today I’m using subselect:

Select * from
   (Select top 3 from TB
   order by Data desc) A
order by Nome

There’s some other better way to do it?

Answer 1

As the bfavaretto commented you should already be using the best option for your requirements.

Note you want to:

1. The last three records.
2. Displays them sorted by name.

For the first requirement you must use the top 3 and a date ordering to return the records you want. With the three records in hand you order them only p/visual issues.

Using a self Join or a table variable can do this but I doubt it’s + efficient (but an Acid test is required to be sure).

The answer of Pedro Laini does not meet the second requirement pq the ordination by date will take precedence in the result presented.

Guilherme Torres' answer is not Reliable may fail if there are "gaps" between Ids (caused by deletion for example). In addition, no correlation was cited between date and Id.

The only other reasonable option (depending on the context) would be to sort the three records returned in the UI or Service.

Answer 2

You can do it this way too:

SELECT TOP 3 FROM TB ORDER BY Data DESC, Nome ASC

Answer 3

Is the ID sequential and the date growing? If it is, you could get the last id - 3.

Select * from TB where id >= (Select max(id) from TB) - 3  order by Nome