last 3 records from a table

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
ranjitbd
Forum Newbie
Posts: 24
Joined: Sun May 03, 2009 1:59 pm

last 3 records from a table

Post by ranjitbd »

Code: Select all

 
// this two lines r working
// imagine that total 6 records r there in the table
 
select * from table_name where id >= 3 and id <= 6;
select * from table_name where id between 3 and 6;
 
//but following two lines r not working. why?
// i want to avoid using group by and order by clause
 
select * from table_name where id >= (select max(id)-3) and id <= (select max(id));
select * from table_name where id between (select max(id)-3) and (select max(id));
 
 
midohioit
Forum Newbie
Posts: 18
Joined: Tue Dec 05, 2006 10:19 am

Re: last 3 records from a table

Post by midohioit »

try this instead:

select * from table_name where id >= (select max(id)-3 from table_name ) and id <= (select max(id) from table_name );

select * from table_name where id between (select max(id)-3 from table_name ) and (select max(id) from table_name );
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Re: last 3 records from a table

Post by superdezign »

What if there are empty IDs? When you remove an item from your database, it is very possible that the ID will not be reused. This is more appropriate for getting the last 3 items from your database table (using MySQL):

Code: Select all

SELECT * FROM table_name ORDER BY id DESC LIMIT 0 3
No idea why you want to avoid ORDER BY. If it is to preserve the order, then I think this would do it:

Code: Select all

SELECT * FROM (SELECT * FROM table_name ORDER BY id DESC LIMIT 0 3) ORDER BY id ASC
Untested.
Post Reply