Reverse order of SQL query output

Question

I have a bit of a problem that I cannot solve. I am running the following through my database using PHP:

$strQuery = "select * from LastResult ORDER BY Date DESC LIMIT 10";

The results all come back fine and as expected. However, I then have to feed these into a line chart, but when I do so, they are obviously displayed in reverse as I have the DB to return them in DESC by Date which means the most recent will be the first returned.

Is there a way that after returning these results I can reverse their order before feeding the data to my chart?

Here is the full query (please don't comment about the use of mysql instead of mysqli; I didn't write that bit)

$strQuery = "select * from LastResult ORDER BY Date DESC LIMIT 10";

$result3 = mysql_query($strQuery) or die(mysql_error());

if ($result3) {
    while($ors4 = mysql_fetch_array($result3)) {
        $NumberResults2 = $ors4['Date'];

        $strQuery = "select AvGoalDifference as Average from LastResult where Date= '$NumberResults2'";

        $result4 = mysql_query($strQuery) or die(mysql_error());
        $ors3 = mysql_fetch_array($result4);

        $strXML .= "<set label='" . $NumberResults2 . "' value='" . $ors3['Average'] . "' />";

        mysql_free_result($result4);
    }
}

mysql_close($link);

$strXML .= "</chart>";
$chart2 = renderChart("charts/Line.swf", "", $strXML, "AverageGD", 500, 260, false, true, true);

echo $chart2;

because this will only return the first 10 results. I need it to return the last 10 but in the opposite order. — geoff_goode
– geoff_goode, Commented Oct 23, 2013 at 12:58

Peter Mortensen · Accepted Answer · 2021-07-20 19:40:37Z

14

You can reorder that resultset by doing an outer select and ordering it the way you want (ASC):

SELECT * FROM (
    select * from LastResult ORDER BY Date DESC LIMIT 10
) as
ORDER BY Date ASC;

If this fails (as it would in PostgreSQL 9.3.4), use an alias for the inner select to make it as below:

SELECT * FROM (
    select * from LastResult ORDER BY Date DESC LIMIT 10
) as foo
ORDER BY foo.Date ASC;

edited Jul 20, 2021 at 19:40

Peter Mortensen

31.4k22 gold badges110 silver badges134 bronze badges

answered Oct 23, 2013 at 12:57

Filipe Silva

21.7k6 gold badges56 silver badges70 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

geoff_goode Over a year ago

That worked perfectly, I looked for ages for that answer, thanks. It did throw up the error 'Every derived table must have its own alias.' but I just had to add in the 'AS' to make it as follows: $strQuery = "select * FROM (select * from LastResult ORDER BY Date DESC LIMIT 10) AS Date ORDER BY Date";

aynber · Accepted Answer · 2013-10-23 12:57:18Z

0

You can do it in the query like this:

$strQuery = "select * FROM (select * from LastResult ORDER BY Date DESC LIMIT 10) ORDER BY Date";

answered Oct 23, 2013 at 12:57

aynber

23.2k9 gold badges57 silver badges69 bronze badges

Comments

WhatsThePoint · Accepted Answer · 2018-05-15 08:23:36Z

0

Try using this;

SELECT * FROM (
    select * from LastResult ORDER BY Date DESC LIMIT 10
)
ORDER BY Date ASC;

edited May 15, 2018 at 8:23

WhatsThePoint

3,63511 gold badges35 silver badges58 bronze badges

answered Oct 23, 2013 at 13:07

Lalbhusan Yadav

1119 bronze badges

Comments

Bhargav Rao · Accepted Answer · 2021-07-21 05:57:38Z

0

Don’t forget to alias the inner selection or MySQL will produce an error.

SELECT * FROM (
    select * from LastResult ORDER BY Date DESC LIMIT 10
) aliasT
ORDER BY Date ASC;

edited Jul 21, 2021 at 5:57

Bhargav Rao

52.6k29 gold badges130 silver badges142 bronze badges

answered Oct 23, 2013 at 13:07

rafael.dev

2462 silver badges5 bronze badges

Collectives™ on Stack Overflow

Reverse order of SQL query output

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related