[SQL] How to SELECT TOP 5 and then the following 5?

Question

Hi guys, as the comments always have it, you can select to show the top 5 comments (which i know how) and if there is more than 5, you can click a link and it will show the following 5 comments. The problem is that i don't know how the code should be for it to show the next 5.

Better idea of what i'm saying Let's say i have 10 comments, i use

SELECT * FROM news ORDER BY ID DESC LIMIT 5

that will show the TOP 5 comments say, the comments with ID 10,9,8,7,6

but what if i want the comments with ID 5,4,3,2,1 ?

Answer 1

SELECT * FROM news ORDER BY ID DESC LIMIT 5,5

Answer 2

I am assuming you are talking about MySQL, so your answer is:

SELECT * FROM news ORDER BY ID DESC LIMIT 5 OFFSET 5

Answer 3

SELECT * FROM news ORDER BY ID DESC LIMIT 5, 5

Assuming that you use MySQL, LIMIT supports two parameters, offset (optional) and row_count:

[LIMIT {[offset,] row_count | row_count OFFSET offset}]

The alternative way using OFFSET is for compatibility with PostgreSQL.

Answer 4

One way to do it that is flavor-agnostic is:

SELECT TOP 5 *
  FROM table
 WHERE pk NOT IN (SELECT TOP (page * 5) pk
                    FROM table
                   ORDER BY pk)
 ORDER BY pk

Answer 5

If you want to show the first five comments with all the text (taken from your comment to Joscha), and then another five comments but with shortened text or just the headlines, you should not use two SQL queries but much rather do this in your application logic.

So select all your news entries:

SELECT id, title, body, date FROM news ORDER BY id DESC LIMIT 10

And then loop over your result. Display the first five different than the next five.

When your want to have something Paginator-like you can start with, say page 0:

$page = isset($_GET['page'] ? intval($_GET['page']) : 0;
SELECT id, title, body, date FROM news ORDER BY id DESC LIMIT 5 OFFSET $page * 5 + 5;

Answer 6

This is a client thing.

Show 5, hide the rest until clicked. Like this site: no call back to get the remaining hidden comments.