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

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;

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

Replies are listed 'Best First'.
Re^2: (OT) MySQL query to limit and sort
by Anonymous Monk on Feb 03, 2006 at 19:52 UTC
    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]
      I think you could get away with selecting the specific columns in the first line, while doing a generic "select *" in the inner select.

      The baz is a table alias for the inner select, which I believe mysql requires (although I haven't verified if you can omit it). It allows you to explicitly name the columns in the outer select, e.g. 

          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

      [download]
      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]
        Hi.

        Sorry to bug you, you've been much help already. I am confused by this particular code.

           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

        [download]
        I know it's pseudo code but I was wondering if perhaps you could show me a small working sample with columns: id, url, images and Total? I am pretty confused by what you mean here. The table is "temp".

        Thank you.

[reply]
[d/l]

In Section Seekers of Perl Wisdom