Sign in to follow this  
AdamGL

[web] MySQL query order

Recommended Posts

Hello all! I am having a slight problem. I want to check how often a certain id number was recorded in one table. Then, using COUNT(), I find the frequency of each number, and order them by frequency, descending.
SELECT `question_id` FROM (

SELECT `question_id`, 
COUNT(`question_id`) AS `freq` 
FROM `dqa_tags` 
GROUP BY `question_id`

) AS `result` 
ORDER BY `freq` DESC 
LIMIT 0, 30

The above works perfectly for my needs. Now, I want to access another table and retrieve the rows which have ids identical to those returned by the above query. If use this:
SELECT * FROM `dqa_questions` 
WHERE `id` = 17 
OR `id` = 18 
OR `id` = 14 
OR `id` = 15 
OR `id` = 16 
OR `id` = 1

The output is automatically ordered by ascending order. I want MySQL to output it in the order I wrote in the query. How do I do this?

Share this post


Link to post
Share on other sites
AFAIK you can't directly. You have to use a dummy value and a big CASE ... END CASE switch.

Something like (untested):

SELECT tbl.*, rank = CASE id WHEN 17 THEN 1 WHEN 18 THEN 2 WHEN 14 THEN 3 WHEN 15 THEN 4 WHEN 16 THEN 5 WHEN 1 THEN 6 END CASE FROM `dqa_questions` tbl
WHERE `id` IN (17, 18, 14, 15, 16, 1) ORDER BY RANK

Share this post


Link to post
Share on other sites
Is the above even moderately feasible time and processing power wise? This query would be used at least once per page load.

Is there an alternative to the entire method I used? Does anyone see a more efficient method?

Thanks Sander :D, this was what I was looking for, although I still would like to find a more efficient method.

Share this post


Link to post
Share on other sites
SELECT * FROM `dqa_questions`
INNER JOIN
(SELECT `question_id`,
COUNT(`question_id`) AS `freq`
FROM `dqa_tags`
GROUP BY `question_id`
) AS `result`
ON `result`.`question_id` = `dqa_questions`.'id'
ORDER BY `result`.`freq` ASC

Share this post


Link to post
Share on other sites
Ahh. I am new to MySQL and I haven't read up on statements such as JOIN or ON. I read the reply and then did some searching and I now understand the query, and it suits my needs perfectly. Thank you both for helping me. It's frustrating searching the web for something you have no idea how to phrase.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

Sign in to follow this