Single Query SELECT last 10 rows ORDER BY ASC?
Moderator: General Moderators
- 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?
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?
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?
Why not DESC and LIMIT and then just reverse the array?
Re: Single Query SELECT last 10 rows ORDER BY ASC?
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.
[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
- 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?
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.
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?
I don't know - subselect?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?}
[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
- 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?
THAT was enough to help me make this MySQL query..
...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! 
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 ASCRe: Single Query SELECT last 10 rows ORDER BY ASC?
Nice and neat! 
Re: Single Query SELECT last 10 rows ORDER BY ASC?
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?
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.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.
There are 10 types of people in this world, those who understand binary and those who don't
Re: Single Query SELECT last 10 rows ORDER BY ASC?
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?
[sql]EXPLAINSELECT temp.idFROM ( SELECT id FROM b ) AS temp [/sql]
[sql]EXPLAINSELECT b.id, ( SELECT val FROM b AS c WHERE c.id = b.id )FROM b[/sql]
and1;"PRIMARY";"<derived2>";"ALL";"";"";"";"";71941;""
2;"DERIVED";"b";"index";"";"PRIMARY";"4";"";71941;"Using index"
[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
Re: Single Query SELECT last 10 rows ORDER BY ASC?
nice explanation, thanks 
- 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?
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?
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?
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 />";
Almost 10 times faster in my scenarios.[/color]0.0031890869140625
0.00042605400085449
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