Is possible to make query to get first 3 comments ORDER BY rating, and rest of comments by timestamp?
Im using simple query to get comments on my script, and i sort it by timestamp, here is example
mysql_query("SELECT * FROM comments WHERE content = '$id' AND approved='1' AND parent_id ='0' ORDER BY timestamp DESC");
by default all comments have 0 rating in database
The query below returns the top 3 rated comments as the first 3 rows, and the most recent comments excluding the top 3 for the rest.
SELECT t1.*
FROM
(
SELECT t2.* FROM (
SELECT c.* , my_order 1 FROM comments c
WHERE content = '$id'
AND approved = '1'
AND parent_id = '0'
ORDER BY rating DESC LIMIT 3
) t2
UNION
SELECT c.* , my_order 2 FROM comments c
WHERE content = '$id'
AND approved = '1'
AND parent_id = '0'
AND id NOT IN (
SELECT id FROM comments
WHERE content = '$id'
AND approved = '1'
AND parent_id = '0'
ORDER BY rating DESC LIMIT 3
)
) t1
ORDER BY
my_order,
(CASE WHEN my_order = 1 THEN rating ELSE timestamp END)
UNION. see stackoverflow.com/questions/421049/…
ORDER BY rating DESC LIMIT 3and then taking the ids of those and throwing them intoWHERE id NOT IN (1, 2, 3) ORDER BY timestamp DESC? You would have to have two separate queries, but since you're doing this in PHP, that should be simple.