Skip to main content
A
Anonymous
September 20, 2009
Answered

Display last 10 entries from database..

  • September 20, 2009
  • 2 replies
  • 881 views

Hi,I would like to display the 10 last entries from my database, I am using PHP and mySQL and Dreamweaver..

the last entries are defined by the 10 highest ID numbers in the database, but there can be gaps in the numbers if an entry has been removed.

What would the WHERE statement look like in my query 

SELECT *
FROM databasetable
WHERE ID = highest number minus 10?
ORDER BY ID ASC

Any ideas greatly appreciated!

Regards, Ken

This topic has been closed for replies.
Correct answer David_Powers

The correct way to do this in MySQL is like this:

SELECT *
FROM databasetable
ORDER BY ID DESC
LIMIT 10

This retrieves the results in reverse order.

If you want the results in ascending order AND you are using MySQL 4.1 or higher, you can do this:

SELECT * from databasetable
WHERE ID > (SELECT ID FROM databasetable ORDER BY ID DESC LIMIT 10, 1)
ORDER BY ID ASC

2 replies

David_Powers
David_PowersCorrect answer
Inspiring
September 20, 2009

The correct way to do this in MySQL is like this:

SELECT *
FROM databasetable
ORDER BY ID DESC
LIMIT 10

This retrieves the results in reverse order.

If you want the results in ascending order AND you are using MySQL 4.1 or higher, you can do this:

SELECT * from databasetable
WHERE ID > (SELECT ID FROM databasetable ORDER BY ID DESC LIMIT 10, 1)
ORDER BY ID ASC
A
Anonymous
September 20, 2009

Mate..

Such and easy and logical explanation Thank you so much!!

itisdesign
itisdesign
Inspiring
September 20, 2009

Hi Ken,

While I have not tested a MySQL example, perhaps the following MSSQL example will help lead you in the right direction:

SELECT *
FROM (SELECT TOP 10 * FROM databasetable ORDER BY ID DESC) AS Temp
ORDER BY ID

This is an SQL Nested SELECT Statement in the FROM Clause.

If we break this apart, and look at the inner select:

SELECT TOP 10 * FROM databasetable ORDER BY ID DESC

This will select the last 10 rows from the database table.  However, they will be sorted in descending order.

If ascending order is necessary, then that select statement is nested within another select statement which orders ascending (this is the example I provided at the top).

Hope that helps!,

-Aaron Neff

P.S. It looks like the following may work in MySQL:

SELECT *

FROM (SELECT * FROM databasetable ORDER BY ID DESC LIMIT 10) as Temp

ORDER BY ID;

Reference 1: http://davidchuprogramming.blogspot.com/2008/08/sql-select-from-select.html

Reference 2: http://lists.mysql.com/mysql/207808

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices