Display last 10 entries from database..

[Guest]

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

[itisdesign]

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

[David_Powers · Accepted solution]

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

[Guest]

Mate..

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