Select Last 20 Order by Ascending - PHP/Mysql

Select last 20 order by ascending - PHP/MySQL

First, select last 20 entries. Then sort them in ascending order. You can easily do this in a single query (with subquery):

select * from (
    select * from your_table order by id desc limit 20
) tmp order by tmp.id asc

mysql get latest date record in ascending order

Why such a complicated query? You can simply do

SELECT logdate,sum(clicks) FROM stats
GROUP BY logdate 
ORDER BY logdate DESC
LIMIT 5

Edit after your comment

SELECT * FROM
(
 SELECT logdate,sum(clicks) FROM stats
 GROUP BY logdate 
 ORDER BY logdate DESC
 LIMIT 5
) data
 ORDER BY logdate ASC

Fiddle

How to select last 5 rows order by id ascending - CakePHP

You can do this by sorting the result by 'id' DESC and limiting the number of records to 5. Then use array_reverse() to reverse the sorting.

$fields = array('ChatPublicMsg.message','ChatPublicMsg.created','ChatPublicMsg.username');
$results = $this->ChatPublicMsg->find('all',array(
       'fields'=>$fields,
       'limit'=>5, 
       'order'=>array('ChatPublicMsg.id DESC')));
$reverse = array_reverse($results['ChatPublicMsg']);
$results['ChatPublicMsg'] = $reverse;

This assumes that 'id' is incremented with each record. Otherwise you need to add a timestamp field to the table and use this field for sorting.

Database mySQL query to sort last ten values from oldest to newest with TIMESTAMP

To get the last ten records sorted that way you could do this:

SELECT *
FROM (SELECT * FROM `tempLog` ORDER BY `time_stamp` DESC LIMIT 10) AS `foo`
ORDER BY `time_stamp` ASC;

last 20 data in ascending order in php

If you need the 20 asc from the all desc you could use

select * from (
     select * from table_name order by id desc 
 ) t order by id asc limit 20

Yii: Select 20 last entries order by id ASC

There is a way without using array_reverse, if you think of using this sql:

SELECT * FROM `comments` `t` 
WHERE id 
in (SELECT id 
     FROM (SELECT id FROM comments Where postID = xyz ORDER BY id DESC LIMIT 20) 
    as q) 
ORDER BY id ASC

which in criteria will become:

$criteria=new CDbCriteria();
$criteria->condition='id in (SELECT id FROM (SELECT id FROM comments Where postID='.$id.' ORDER BY id DESC LIMIT 20) as q)';
$criteria->order='id ASC';

Update:

With your original query, you could have also used findBySql :

$sql='SELECT * FROM (SELECT * FROM comments  WHERE postID= :postid  ORDER BY id DESC LIMIT 20) q ORDER BY id ASC';
$params=array('postid'=>$id);
$comments=Comment::model()->findAllBySql($sql,$params);

The performance of this query was better than my previous query.

Get the last record first, then the rest in the ascending order

One option sorts using a subquery to find the last description:

SELECT id, description
FROM yourTable
ORDER BY
    CASE WHEN id = (SELECT MAX(id) FROM yourTable) THEN 0 ELSE 1 END,
    id;

If you are using MySQL 8 or later, then we can try avoiding the subquery by using MAX as an analytic function:

SELECT id, description
FROM yourTable
ORDER BY
    IF(id = MAX(id) OVER (), 0, 1),
    id;

MySQL - Retrieve 7 most recent timestamps, sorted oldest first

two selects could work in this case. it would at least be one possible way to achieve what you want. i'm not sure if it would the best way.

i'm assuming your table has an id field.

select * from records 
  where id in(select id from records order by timestamp desc limit 7) 
  order by timestamp asc;

this lets you get the latest 7 rows in the inner select, then sort them in ascending order.

---

Related Topics