Re^2: (OT) MySQL query to limit and sort

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?

Comment on Re^2: (OT) MySQL query to limit and sort

Replies are listed 'Best First'.
Re^3: (OT) MySQL query to limit and sort by rhesa (Vicar) on Feb 03, 2006 at 21:39 UTC
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} }` [download] 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]

Replies are listed 'Best First'.

Re^3: (OT) MySQL query to limit and sort
by rhesa (Vicar) on Feb 03, 2006 at 21:39 UTC

Re: (OT) MySQL query to limit and sort

Order by foo, bar desc, baz

sort {
    $a->{foo} cmp $b->{foo}
    ||
    $b->{bar} cmp $a->{bar}
    ||
    $a->{baz} cmp $b->{baz}
}
[download]

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]