1

See the below example,

create table data(name varchar, value int);
insert into data values('joe',1);
insert into data values('bob',2);
insert into data values('jane',3);
insert into data values('anne',4);
insert into data values('kate',5);

And if I Execute 

select * from data limit 2;

Will Get

 name | value 
------+-------
 joe  |     1
 bob  |     2
  (2 rows)

So,How Can I Get the Last 2 Rows in select * from data?

What I'm expecting is....

  name | value 
 ------+-------
  anne |     4
  kate |     5
   (2 rows)
Improve this question
1
  • 2
    Your assumption is incorrect. If ORDER BY is not given, the rows are returned in whatever order the system finds fastest to produce. postgresql.org/docs/current/static/sql-select.html -- your first (example) result-set is not guaranteed to be the ones with the least values in the value column f.ex. Commented Sep 1, 2014 at 9:38

6 Answers 6

Reset to default
4

You have two options according to your need i.e,

   select * from data order by value desc limit 2

Or

LIMIT and OFFSET

if you want the 4th and 5th row just offset the first 3 so that the 4th row becomes the start of our set and you can specify a limit to say that you only want 2 rows from that.

   select * from data offset 3 limit 2;
   /* The order of LIMIT and OFFSET does not matter. This gives the same result */
   select * from data limit 2 offset 3;
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

3

To get the x last rows, example with x=10, use offset alone, with count:

SELECT *
FROM data
ORDER BY value ASC
OFFSET (SELECT count(*) FROM DATA)-10

I let you check if the offset is not negative...

Improve this answer

4 Comments

I tried this and got: [Amazon](500310) Invalid operation: argument of OFFSET must not contain subqueries; ...??
This is good, for as you can make the query in any order and get de last N records of it. May be postgres has a lack of a "last" option for the fetch clause, or the need of offset to accept negative values.
@Angus, the limit and offset clauses admit expressions (that must return positive integers). Subquery enclosed in parenthesis is an expression and can be used. You can try it on a postgresql console.
SELECT * FROM data ORDER BY value ASC OFFSET GREATEST((SELECT count(*) FROM DATA)-10, 0) LIMIT 10
3

I know I'm answering a six year old question and I know the accepted answer says to use an offset, however that's only going to be useful if you know the length of the table. Looking at the wording of the question and the example given, I assumed that like myself, the author wanted the last entries in a table based on an id or other value in the order that they were entered. The best solution I've found so far that orders the values as expected is using a subquery as follows:

SELECT * FROM ( SELECT * FROM data ORDER BY VALUE DESC LIMIT 2) AS _ ORDER BY VALUE ASC;

You'd substitute VALUE for whichever column you'd want to sort by to get the "last" entries. In my experience, this method is an order of magnitude quicker than using a count to find an offset.

Improve this answer

Comments

1

Make use of Order by Clause

select * from data order by value desc limit 2;

OR

select top 2 * from data order by value desc ;
Improve this answer

Comments

1

You can achieve it using Order by clause desc

SELECT *
FROM data
ORDER BY value DESC limit 2;
Improve this answer

1 Comment

An index on value DESC can be useful if you do this a lot, too.
0

like does Krishraj Rana you can try this function for get the last row with "limit 1" and order by "x col" desc, complete it "by x_col desc limit 1".

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.