Page 1 of 2
Single Query SELECT last 10 rows ORDER BY ASC?
Posted: Thu Jan 15, 2009 2:01 am
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?
Re: Single Query SELECT last 10 rows ORDER BY ASC?
Posted: Thu Jan 15, 2009 2:04 am
by papa
Why not DESC and LIMIT and then just reverse the array?
Re: Single Query SELECT last 10 rows ORDER BY ASC?
Posted: Thu Jan 15, 2009 2:06 am
by Eran
Yep, papa is on the money

Use
array_reverse() on the results.
Re: Single Query SELECT last 10 rows ORDER BY ASC?
Posted: Thu Jan 15, 2009 2:06 am
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.
Re: Single Query SELECT last 10 rows ORDER BY ASC?
Posted: Thu Jan 15, 2009 2:30 am
by JAB Creations
You guys are INSANE!
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.

Re: Single Query SELECT last 10 rows ORDER BY ASC?
Posted: Thu Jan 15, 2009 2:39 am
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]
Re: Single Query SELECT last 10 rows ORDER BY ASC?
Posted: Thu Jan 15, 2009 2:56 am
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!

Re: Single Query SELECT last 10 rows ORDER BY ASC?
Posted: Thu Jan 15, 2009 2:59 am
by papa
Nice and neat!

Re: Single Query SELECT last 10 rows ORDER BY ASC?
Posted: Thu Jan 15, 2009 3:10 am
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.
Re: Single Query SELECT last 10 rows ORDER BY ASC?
Posted: Thu Jan 15, 2009 3:16 am
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.
Re: Single Query SELECT last 10 rows ORDER BY ASC?
Posted: Thu Jan 15, 2009 3:23 am
by Eran
By dependant subquery do you mean when it appears in the WHERE clause? I wasn't aware of that distinction.
Re: Single Query SELECT last 10 rows ORDER BY ASC?
Posted: Thu Jan 15, 2009 3:38 am
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;""
Re: Single Query SELECT last 10 rows ORDER BY ASC?
Posted: Thu Jan 15, 2009 3:57 am
by Eran
nice explanation, thanks

Re: Single Query SELECT last 10 rows ORDER BY ASC?
Posted: Thu Jan 15, 2009 5:27 am
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?
Re: Single Query SELECT last 10 rows ORDER BY ASC?
Posted: Thu Jan 15, 2009 7:21 am
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]