Order List by two Parameters

Pull top X from database, then sort alphabetically

[Jeremy_H]

I have a large MySQL database of "Frequently Asked Questions".

This database has a rows that contain a question, answer, and the number of times the question was asked.

I would like to grab the 20 most asked questions from this database, but to arrange this list alphabetically by question.

I can pull the 20 most asked questions, but they are not arranging themselves alphabetically.

This is what I have so far:

mysql_query("SELECT * FROM faq ORDER BY count DESC, question LIMIT 20");

Thanks for any help.

[LifeinAsia]

I'm not sure about mySQL, but you should be able to do something like:
SELECT *
FROM FAQ
WHERE question IN (SELECT question, count FROM FAQ ORDER BY count DESC LIMIT 20)
ORDER BY Question

By the way, you don't really have a field named "count" do you?

[Jeremy_H]

Thanks for your reply,

It seems like that would only give my the top 20 most requested files, which is great, but then how do I resort this short list into alphabetical order?

You did bring up a good point. I really do not have a field called count, I just used that title for this question in an attempt to simplify the question.

[LifeinAsia]

but then how do I resort this short list into alphabetical order?

That would be the the "ORDER BY Question" part (I assumed the Question field was what you wanted to sort alphabetically).

Actually, it's good that you do NOT have a field called Count. As a reserved word, it shouldn't work for most implementations of SQL. And even if it does, it's just bad design.

[Jeremy_H]

Sorry, I completely misread your post!

Per your advice I renamed count to asked in this example and tried the following format:

mysql_query("SELECT * FROM faq WHERE question IN (SELECT * FROM faq ORDER BY asked DESC LIMIT 20) ORDER BY question");

The format makes total sense, but unfortunately doesn't seem to be working for me, at least in the mySQL environment.

[LifeinAsia]

What part doesn't seem to be working?

[Jeremy_H]

I don't get a direct error, but I get an error on the next line where I have:

while($row=mysql_fetch_array($result)) {...}

The error I get is "mysql_fetch_array(): supplied argument is not a valid MySQL result resoure"

This second line of code works fine when the line above was set to $result=mysql_query(...) of previous statements, but not of the latest statement.

[LifeinAsia]

I don't know enough about mySQL. Can you dump out all the results to see if you're getting what you ecpect?

[Woz]

Wherever possible, use the database to do database type work.

Assuming you are using mySQL 5 or above, create a view to filter the top 20 using SELECT * FROM faq ORDER BY asked DESC LIMIT 20.

You could then use your scripting to pull SELECT * FROM viewFAQtop20 ORDER BY question.

When you get that sorted, you could take it the next step by creating a second view to reorder the initial results and simply pull the data from that second view all ready to go. Others more better than I at this could probably put all of that into a single view.

Onya
Woz