Select from the last records sorted by 1 field

Asked

Viewed 2,032 times

2

I have the following table structure

inserir a descrição da imagem aqui

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?

sql-server tsql

  • It could also be a self-join. You need to test both options to see which has the best performance (I’m currently without SQL Server). Anyway, it seems a good solution.

3 answers

0


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.

0

You can do it this way too:

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

  • This only works if the 3 records have equal dates.

-1

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

  • Failed if any of the last records are deleted for example

Browser other questions tagged sql-server tsql

You are not signed in. Login or sign up in order to post.