Single Query SELECT last 10 rows ORDER BY ASC?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

User avatar
JAB Creations
DevNet Resident
Posts: 2341
Joined: Thu Jan 13, 2005 6:44 pm
Location: Sarasota Florida
Contact:

Single Query SELECT last 10 rows ORDER BY ASC?

Post by JAB Creations »

In a single query I'd like to SELECT the last ten rows ordered by ASC.

The issue is I could sort by DESC and use LIMIT to get the last ten rows though they would be sorted backwards.

I could do two SELECT queries and do a count though I'd like to see if I can learn something new.

Thoughts please?
User avatar
papa
Forum Regular
Posts: 958
Joined: Wed Aug 27, 2008 3:36 am
Location: Sweden/Sthlm

Re: Single Query SELECT last 10 rows ORDER BY ASC?

Post by papa »

Why not DESC and LIMIT and then just reverse the array?
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Single Query SELECT last 10 rows ORDER BY ASC?

Post by Eran »

Yep, papa is on the money :)
Use array_reverse() on the results.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Single Query SELECT last 10 rows ORDER BY ASC?

Post by VladSun »

You can use a query like this:
[sql]SELECT    *FROM    (        SELECT            *        FROM            a    ) AS b[/sql]

So you can reorder results from the inner select in the outer select.
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
JAB Creations
DevNet Resident
Posts: 2341
Joined: Thu Jan 13, 2005 6:44 pm
Location: Sarasota Florida
Contact:

Re: Single Query SELECT last 10 rows ORDER BY ASC?

Post by JAB Creations »

You guys are INSANE! :mrgreen:

VladSun, what is that called exactly? If (it's not called anything special (so I can look it up for more examples)) {could you please provide an example with instead of A and B as this_column and this_row in example?} else {what is the name of what that method is called?}

pytrin, thanks for the link, I'm going to try papa's suggestion.

Right now I'm working on fetching the last ten messages in my chat room when it's first initiated. :)
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Single Query SELECT last 10 rows ORDER BY ASC?

Post by VladSun »

JAB Creations wrote:VladSun, what is that called exactly? If (it's not called anything special (so I can look it up for more examples)) {could you please provide an example with instead of A and B as this_column and this_row in example?} else {what is the name of what that method is called?}
I don't know - subselect?
[sql]SELECT    b.idFROM    (        SELECT            a.id        FROM            a        ORDER BY            a.id DESC        LIMIT            10    ) AS bORDER BY    b.id ASC[/sql]
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
JAB Creations
DevNet Resident
Posts: 2341
Joined: Thu Jan 13, 2005 6:44 pm
Location: Sarasota Florida
Contact:

Re: Single Query SELECT last 10 rows ORDER BY ASC?

Post by JAB Creations »

THAT was enough to help me make this MySQL query..

Code: Select all

SELECT *FROM ( SELECT ch.id, ch.comments, ch.DATE, ua.user_username, ua.user_username_base FROM chat AS chLEFT JOIN user_accounts AS ua ON (ch.id_member = ua.user_id)ORDER BY ch.id DESCLIMIT 0 , 10 ) AS bORDER BYb.id ASC
...and put a big smile on my face! Thanks! I'm sure I'll need to reverse an array some day so I'll keep that in the back of my head. Thanks for all the replies! :D
User avatar
papa
Forum Regular
Posts: 958
Joined: Wed Aug 27, 2008 3:36 am
Location: Sweden/Sthlm

Re: Single Query SELECT last 10 rows ORDER BY ASC?

Post by papa »

Nice and neat! :)
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Single Query SELECT last 10 rows ORDER BY ASC?

Post by Eran »

Just take notice - subqueries in MySQL are not very efficient. In this case it will probably be much more efficient to arrange the data in PHP.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Single Query SELECT last 10 rows ORDER BY ASC?

Post by VladSun »

pytrin wrote:Just take notice - subqueries in MySQL are not very efficient. In this case it will probably be much more efficient to arrange the data in PHP.
It's not efficient if it's a dependant subselect - i.e. the subselect uses fields from the outer select, which is not the case here. And I'm pretty sure SQL engine will provide much faster sorting than PHP code - though it's not very important (only 10 rows...) in this case.
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Single Query SELECT last 10 rows ORDER BY ASC?

Post by Eran »

By dependant subquery do you mean when it appears in the WHERE clause? I wasn't aware of that distinction.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Single Query SELECT last 10 rows ORDER BY ASC?

Post by VladSun »

[sql]EXPLAINSELECT      temp.idFROM    (    SELECT          id    FROM         b    ) AS temp [/sql]
1;"PRIMARY";"<derived2>";"ALL";"";"";"";"";71941;""
2;"DERIVED";"b";"index";"";"PRIMARY";"4";"";71941;"Using index"
and

[sql]EXPLAINSELECT    b.id,    (         SELECT               val         FROM              b AS c         WHERE              c.id = b.id    )FROM    b[/sql]
1;"PRIMARY";"b";"index";"";"PRIMARY";"4";"";71941;"Using index"
2;"DEPENDENT SUBQUERY";"c";"eq_ref";"PRIMARY";"PRIMARY";"4";"test.b.id";1;""
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Single Query SELECT last 10 rows ORDER BY ASC?

Post by Eran »

nice explanation, thanks :)
User avatar
jaoudestudios
DevNet Resident
Posts: 1483
Joined: Wed Jun 18, 2008 8:32 am
Location: Surrey

Re: Single Query SELECT last 10 rows ORDER BY ASC?

Post by jaoudestudios »

Just caught the end of the thread. Good question JAB, funny enough I was thinking the same thing a few hours ago for an online chat system I am writing.

Great answers guys. I have created a dummy database with over 5 million messages (I plan to log the messages as a history, so other users can search it), so tonight I will try and bench the php and mysql version of sorting - I'll post some timings.

JAB we might have to compare some ideas! I only started mine last night so early days. But plan for it to be a simple chat room. Using Jquery for AJAX. How are you doing yours?
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Single Query SELECT last 10 rows ORDER BY ASC?

Post by VladSun »

WRONG! Read next posts ...

Code: Select all

function microtime_float() 
{ 
    list($usec, $sec) = explode(" ", microtime()); 
    return ((float)$usec + (float)$sec); 
} 
 
 
$time_start = microtime_float();
$result = mysql_query('
    select 
        SQL_NO_CACHE * 
    from 
        b 
    order by 
        val desc 
    limit 500
');
 
$a = array();
while($row = mysql_fetch_assoc($result))
    $a[] = $row;
array_reverse($a);
$time_end = microtime_float();
$time = $time_end - $time_start;
echo "$time <br />";
 
$time_start = microtime_float();
$result = mysql_query('
    select 
        SQL_NO_CACHE * 
    from  
        (
            select 
                SQL_NO_CACHE * 
            from 
                b 
            order by 
                val desc 
            limit 500
        )
    order by 
        val asc
');
$time_end = microtime_float();
$time = $time_end - $time_start;
echo "$time <br />";
 
0.0031890869140625
0.00042605400085449
Almost 10 times faster in my scenarios.[/color]
Last edited by VladSun on Thu Jan 15, 2009 7:55 am, edited 2 times in total.
There are 10 types of people in this world, those who understand binary and those who don't
Post Reply