Two sorts on 1 mysql query

[iluvperl]

I have a question about sorting a Mysql query twice.

I need to first sort the columns by ID so that only the newest IDs are brought back. Since it's an autoincrement, the largest 10 of the IDs are the newest.

Then I need to sort this by the column Total (highest number first).

I tried the below line but it doesn't work. It's not erroring out but it's not sorting properly. I am new to MySQL.

Can anyone help?

my $data = qq(SELECT id, url, altavista, yahoo, msn, teoma, google, alltheweb, Total FROM ( SELECT id, url, altavista, yahoo, msn, teoma, google, alltheweb, Total FROM temp ORDER BY id DESC LIMIT 10  ) baz   ORDER BY Total desc);

 

[TonyGroves]

That query looks OK. What is wrong with the sorting? Is it the "top 10" or the "total" sort?

P.S. There is a MySQL forum here.

 

[iluvperl]

It sorts the last 10 chunks of data but doesn't begin to sort on the Total field.

 

[TonyGroves]

If you run that query in a dedicated database client program, does it sork OK?

 

[TonyGroves]

s/sork/sort/;

 

[iluvperl]

What do you mean a dedicated database? I'm using MySQL.

 

[TonyGroves]

I mean a client program other than your own Perl program. For example, the "mysql" command-line client, or a GUI program like MySQL Query Browser.

 

[iluvperl]

No, all I have is my Perl program.

 

[TonyGroves]

If the same query works in another program, like MySQL Query Browser (free from the MySQL site), then the problem lies in your program logic, not in the query.

 

[iluvperl]

Sorry, we must be thinking of two different things. It's not working ANYWHERE. The only thing I am doing to launch this query is in my cgi script via my browser.

And it doesn't sort (it does, however, limit to the last 10).

 

[icrf]

The only place you've tried is your CGI script b/c it's the only thing you have right now. Tony was just suggesting you try it with another tool/interface to make sure it wasn't perl munging something up.

While I don't see anything wrong with the initial query, you could try something like this:

SELECT id, url, altavista, yahoo, msn, teoma, google, alltheweb, Total FROM temp WHERE id in (SELECT id FROM temp ORDER BY id DESC LIMIT 10)

- Andrew
Text::Highlight - A language-neutral syntax highlighting module in Perl
also on SourceForge including demo