Select rows within a certain range

Question

I've attempted to search but not sure I'm getting the keywords correct for this.

I want to select rows in desc order that aren't the last 5 rows but the 5 before that!

So if I had 140 rows, I want rows 131-135.

The following ets me 136-140.

SELECT id, title, content, date FROM tbl_news ORDER BY id DESC LIMIT 5

How could I change the limit value to achieve 131-135?

Thanks.

Answer 1

You want to use the two parameter limit:

SELECT id, title, content, date FROM tbl_news ORDER BY id DESC LIMIT 5, 5

This will give you the first 5 rows after row 5(so rows 6, 7, 8, 9, 10) which is the 5 rows before the last 5 like you want.

Edit: Fiddle here: http://sqlfiddle.com/#!2/9113a/1

It should return id's 4-8 as there are 12 records here.

Answer 2

Limit can take 2 parameters, offset (which is zero based) and quantity, so you can use this:

SELECT id, title, content, date FROM tbl_news ORDER BY id DESC LIMIT 10, 5

To get 5 rows, starting at row 10

For you case, you can do

SELECT id, title, content, date FROM tbl_news ORDER BY id ASC LIMIT 129, 5

Answer 3

SELECT id, title, content, date FROM tbl_news ORDER BY id DESC LIMIT 5,5;

Answer 4

SELECT id, title, content, date FROM tbl_news ORDER BY id DESC LIMIT 10,5

Answer 5

Does this work for you?

select top 5 id
from tbl_news
where id not in
(
    select top 5 id
    from tbl_news
    order by id desc
)
order by id desc