|
Re: (OT) MySQL query to limit and sort
by rhesa (Vicar) on Feb 03, 2006 at 19:38 UTC
|
Do you mean something like this?
select id, Total
from (
select id, Total
from bar
order by id desc
limit 10
) baz
order by Total desc;
I think doing a simple select id, Total from bar order by id desc, Total desc limit 10; won't work since sorting by id is already exhaustive. | [reply]
[d/l]
[select] |
|
|
Hi. Thanks for your code. One quick question.
I have like 5 columns total, including id and Total. Would I select these on the first line, inside the from() or both?
Sorry, that confuses me a little bit. Oh, and what is baz on the 2nd to the last line?
| [reply] |
|
|
select baz.id, foo.name
from foo, -- regular table
(
select id, frob from bar
) baz -- a sub-select
where foo.id = baz.frob -- something that joins the two
You can see there that it helps to disambiguate between foo.id and baz.id, that's why it's good that sub-selects can be named.
| [reply]
[d/l] |
|
|
|
|
Re: (OT) MySQL query to limit and sort
by shiza (Hermit) on Feb 03, 2006 at 20:54 UTC
|
SELECT id, total
FROM table
ORDER BY id, total DESC
LIMIT 10;
That should do the trick. It orders by id (ascending which is the default), and then orders by total in descending order.
If you're using MySQL see their manual.
If you're using PostgreSQL see their manual. | [reply]
[d/l] |
|
|
I tried your code but it only used the first 10 inserted rows. I had to change it to ORDER BY id DESC. Now it shows the newest 10 but it doesn't sort them by the total at all. Any idea why?
| [reply] |
|
|
As I already commented in Re: (OT) MySQL query to limit and sort, the sort on id is already exhaustive, meaning that sorting on total doesn't change anything. That is, every id value is different, so Total is never considered as a sorting criterion.
Remember that Order by foo, bar desc, baz is similar to this in Perl:
sort {
$a->{foo} cmp $b->{foo}
||
$b->{bar} cmp $a->{bar}
||
$a->{baz} cmp $b->{baz}
}
If all the "foo" fields are distinct, the sort short-circuits and never considers bar or baz.
And that's exactly why I offered the sub-select as a solution: first grab the 10 most recent id's, then sort those on Total. You have to do the ordering in two steps, no way around that.
| [reply]
[d/l]
[select] |
Re: (OT) MySQL query to limit and sort
by saberworks (Curate) on Feb 03, 2006 at 22:12 UTC
|
UPDATE: Sorry I put a broken query... here is the fixed one:
SELECT id, total
FROM some_table
WHERE id > (SELECT MAX(id) FROM some_table) - 10
ORDER BY total DESC
(old one here for historical purposes):
SELECT id, total
FROM some_table
WHERE id > (SELECT MAX(id) - 10)
ORDER BY total DESC
| [reply]
[d/l]
[select] |
|
|
Using that I don't get any rows back.
my $data = qq[SELECT id, url, images, Total FROM temp WHERE id > (SEL
+ECT MAX(id) - 10) ORDER BY Total DESC];
| [reply]
[d/l] |
|
|
SELECT id, total
FROM some_table
WHERE id > (SELECT MAX(id) FROM some_table) - 10
ORDER BY total DESC
| [reply]
[d/l] |
|
|