Geek Answers Handbook

Select the last n lines without using order by condition

I want to get the last n rows from a table in a Postgres database. I do not want to use the sentence ORDER BY, because I want to have a general request. Anyone have any suggestions?

One request will be appreciated since I do not want to use the FETCHPostgres cursor .

+5
source share
2 answers

To get what you expect from the Lukas solution (as of November 1, 2011), luck was a success . In RDBMS, by definition, there is no “natural order” . You depend on implementation details that are subject to change with the new version without notice. Or reset / restore can change this order. It may even change due to blue when the db statistics change and the query scheduler selects a different plan that leads to a different row order.

the correct way to get the rows "last n" is to have a timestamp or sequence ORDER BYcolumn and that column. Each RDBMS that you can think of has ORDER BY, therefore, it is "general" as you receive it.

I :

ORDER BY , .

Lukas LIMIT, - (, SQL Server TOP n LIMIT), ORDER BY .

, , , SQL, - , . MySQL, , .

+9

!

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

t2.r - , t2.c - . :n n , . .

: , :

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
+5

More articles:


All Articles