CODEWITHSUNDEEP
phpmysqlselectsql-order-by
Dylan Cross
Dylan Cross

Reputation: 5986

MYSQL select last 3 rows, order by ASC

I just want to select the newest 3 comments on a post, and have them ordered in ASC order.

This selects the last 3 rows, however I need them in the reverse order:

mysql_query("
SELECT * FROM comments WHERE postID='$id' AND state='0' ORDER BY id DESC LIMIT 3")

Upvotes: 7

Views: 22263

Answers (4)

Sushil
Sushil

Reputation: 1

SELECT * FROM comments LIMT((SELECT COUNT(*) FROM comments)-2,3) ORDER BY id;

As LIMIT's first argument(OffSet) start from 0th index, and second arguments gives the no. of records should be picked up.

Upvotes: -1

Kakai Elvis
Kakai Elvis

Reputation: 1

$result = mysqli_query($con,"SELECT * FROM (SELECT * FROM messeges WHERE username='{$_SESSION['username']}' ORDER BY id DESC LIMIT 3) t ORDER BY id ASC");

Upvotes: -3

cegfault
cegfault

Reputation: 6632

This can also be done just in PHP, without modifying the SQL query, by simply iterating backwards through the result set:

$res = mysql_query(...);
for($i=mysql_num_rows($res)-1; $i>=0; $i--) {
    //do whatever
}

I confess I don't know what the performance difference is (if any), but it's just another option that might suite you.

Upvotes: 1

Sergio Tulentsev
Sergio Tulentsev

Reputation: 230376

You can reverse sort it later.

SELECT * 
FROM (SELECT * FROM comments
      WHERE postID='$id' 
        AND state='0' 
      ORDER BY id DESC 
      LIMIT 3) t
ORDER BY id ASC;

Upvotes: 17

Related Questions