in reply to (OT) MySQL query to limit and sort

SELECT id, total
FROM table
ORDER BY id, total DESC
LIMIT 10;

[download]

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.

Replies are listed 'Best First'.
Re^2: (OT) MySQL query to limit and sort
by Anonymous Monk on Feb 03, 2006 at 21:32 UTC
    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}
}

      [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]

In Section Seekers of Perl Wisdom