CODEWITHSUNDEEP
phpmysqlsql
Lloyd
Lloyd

Reputation: 43

How to get the data before the max id

I'm doing an info system that has announcements, so I wanted to get the data from announce table, first should be the one with max id of course since it is the latest announcement and here is my code for it:

SELECT image FROM announce where id=(SELECT max(id) FROM announce);

Now I wanted to get the data before the max id and I'm using this code here: 

SELECT image FROM announce where id=(SELECT max(id)-1 FROM announce);

But it only works if there are no deleted rows,if there are some deleted rows it doesn't work like max id is 10 and the id's 9,8,7 are deleted. That means the present id's are: 1,2,3,4,5,6,10, how will I be able to get the data in the id 6,5,4,3,2,1?

Upvotes: 3

Views: 110

Answers (2)

potashin
potashin

Reputation: 44581

You can just use one query with order by and limit instead of the two queries you use:

select image 
from announce 
order by id desc 
limit 2

In case you want to query them separately, you can change limit 2 to limit 1, 1 for you second query to work as you wish.

Upvotes: 1

Qirel
Qirel

Reputation: 26450

If you ORDER the query by descending order (DESC), and have an OFFSET of one (OFFSET 1), you would get the second to last row. And offset of two would give the row before that, and so on. This also uses LIMIT 1, so we just get one row. LIMIT 1, 1 is the same as LIMIT 1 OFFSET 1.

SELECT image
FROM announce
ORDER BY id DESC
LIMIT 1, 1

Upvotes: 2

Related Questions