Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Select last n rows without use of order by clause

I want to fetch the last n rows from a table in a Postgres database. I don't want to use an ORDER BY clause as I want to have a generic query. Anyone has any suggestions?

A single query will be appreciated as I don't want to use FETCH cursor of Postgres.

like image 968
Mital Pritmani Avatar asked Oct 31 '11 06:10

Mital Pritmani


People also ask

How do I display last n rows in SQL?

SELECT * FROM ( SELECT * FROM yourTableName ORDER BY id DESC LIMIT 10 )Var1 ORDER BY id ASC; Let us now implement the above query. mysql> SELECT * FROM ( -> SELECT * FROM Last10RecordsDemo ORDER BY id DESC LIMIT 10 -> )Var1 -> -> ORDER BY id ASC; The following is the output that displays the last 10 records.

How do I get the last 3 rows of a SQL table?

The TOP clause in SQL Server returns the first N number of records or rows from a table. Applying the ORDER BY clause with DESC, will return rows in descending order. Hence, we get the last 3 rows.

How do I get the last 5 rows of a SQL table?

METHOD 1 : Using LIMIT clause in descending order As we know that LIMIT clause gives the no. of specified rows from specifies row. We will retrieve last 5 rows in descending order using LIMIT and ORDER BY clauses and finally make the resultant rows ascending.

Can we use SELECT without FROM clause?

Although the SQL standard doesn't allow a SELECT statement without a FROM clause, pretty much every other database does support the construct of selecting and expression without a FROM clause.


2 Answers

That you get what you expect with Lukas' solution (as of Nov. 1st, 2011) is pure luck. There is no "natural order" in an RDBMS by definition. You depend on implementation details that could change with a new release without notice. Or a dump / restore could change that order. It can even change out of the blue when db statistics change and the query planner chooses a different plan that leads to a different order of rows.

The proper way to get the "last n" rows is to have a timestamp or sequence column and ORDER BY that column. Every RDBMS you can think of has ORDER BY, so this is as 'generic' as it gets.

The manual:

If ORDER BY is not given, the rows are returned in whatever order the system finds fastest to produce.

Lukas' solution is fine to avoid LIMIT, which is implemented differently in various RDBMS (for instance, SQL Server uses TOP n instead of LIMIT), but you need ORDER BY in any case.

like image 169
Erwin Brandstetter Avatar answered Sep 28 '22 09:09

Erwin Brandstetter


Use window functions!

select t2.* from (
  select t1.*, row_number() over() as r, count(*) over() as c
  from (
    -- your generic select here
  ) t1
) t2
where t2.r + :n > t2.c

In the above example, t2.r is the row number of every row, t2.c is the total records in your generic select. And :n will be the n last rows that you want to fetch. This also works when you sort your generic select.

EDIT: A bit less generic from my previous example:

select * from (
  select my_table.*, row_number() over() as r, count(*) over() as c
  from my_table
  -- optionally, you could sort your select here
  -- order by my_table.a, my_table.b
) t
where t.r + :n > t.c
like image 20
Lukas Eder Avatar answered Sep 28 '22 07:09

Lukas Eder