Query - Top 20 results in ascending alphabetical order 1

[Nematoth]

I have a table which is a list of categories in an online directory.. each time the category is visited in increases the hits column by one..

I want to get the top 20 results (ie. the top twenty most popular categories) and then sort them into a list alphabetically.

I tihnk I have to use a nested select but when I do this I only get the top 20 results sorted by alphabet not by hits..

Here is the table structure:

#
# Table structure for table `category`
#

CREATE TABLE category (
  autoID mediumint(9) NOT NULL auto_increment,
  title varchar(100) NOT NULL default '',
  hits int(11) NOT NULL default '0',
  PRIMARY KEY  (autoID)
) TYPE=MyISAM;

Can anyone help with this?

TIA.

N.

Nematoth

http://www.etv.com.my

 

[swampBoogie]

select title,hits
 from 
  (select title,hits
     from category
    order by hits desc
    limit 20) dt
order by title

 

[Nematoth]

Thats giving me an error mate... what does the "DT" do?

Nematoth

http://www.etv.com.my

 

[Nematoth]

I am running version MySQL 3.23.58

Could this be the problem? I saw another post somewhere stating that nested select only works on version 4.1 and above..

Is there a workaround?

Nematoth

http://www.etv.com.my

 

[johnwm]

Use a temp table?

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller

 

[TonyGroves]

You would have to use a temporary table.
[tt]
CREATE TEMPORARY TABLE t AS
SELECT * FROM category ORDER BY hits DESC LIMIT 20;
SELECT * FROM t ORDER BY title;

...

DROP TABLE t;
[/tt]

 

[Nematoth]

Great! Thats the way! Thanks John.

This is what I did:

CREATE TABLE new_tbl SELECT title, hits FROM category ORDER BY hits DESC LIMIT 20

SELECT title,hits FROM new_tbl ORDER BY title ASC

DROP TABLE new_tbl

Nematoth

http://www.etv.com.my