CODEWITHSUNDEEP
sqlselect
Crays
Crays

Reputation: 2508

How to SELECT TOP 5 and then the following 5?

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 ?

Upvotes: 3

Views: 889

Answers (5)

gbn
gbn

Reputation: 432631

This is a client thing.

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

Upvotes: 1

Sebastian Hoitz
Sebastian Hoitz

Reputation: 9393

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;

Upvotes: 0

Peter Lang
Peter Lang

Reputation: 55594

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.

Upvotes: 4

Paulo Santos
Paulo Santos

Reputation: 11587

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

Upvotes: 1

Joscha
Joscha

Reputation: 4693

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

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

Upvotes: 2

Related Questions