How to make this Mysql query work?

Question

My query:

SELECT * 
FROM forum_topics 
WHERE cat_id IN(1,2,3,4,5,6,7) 
ORDER BY last_message DESC 
LIMIT 7

I want to get the biggest and only one value of each cat_id (7 values total). How to correct this query to make it work if it's even possible?

There is forum topics and each has value last_message and I want to get the latest topic's message's time. Hope it's clear.

Thank you

score 1 · Accepted Answer · answered Sep 13 '10 at 16:32

1

MySQL doesn't have analytical function support, which is what you're really after:

SELECT x.*
  FROM (SELECT ft.*,
               CASE 
                  WHEN @cat_id = ft.cat_id THEN @rownum := @rownum + 1
                  ELSE @rownum := 1
               END AS rank,
               @cat_id = ft.cat_id
          FROM FORUM_TOPICS ft
          JOIN (SELECT @rownum := 0, @cat_id := -1) r
      ORDER BY ft.cat_id, ft.last_message DESC)
 WHERE x.rank = 1

This will provide a computed column called "rank", where the most recent row based on the last_message column will have the value of "1" per cat_id value. The outer query only gets the records whose rank value is one...

answered Sep 13 '10 at 16:32

OMG Ponies

325,700
82
523
502

1

This query looks ugly as hell :/ – good_evening Sep 13 '10 at 18:41
1

@hey: Blame MySQL for not supporting analytic functions yet. Until then, expect hacks like these. Also, you should not use `SELECT *` - because I didn't know the columns in FORUM_TOPICS, you'll get all of them... plus the `rank` and the column for assigning the cat_id value in the output. – OMG Ponies Sep 13 '10 at 18:46
And Pony, please check this thread, maybe you will able to help: http://stackoverflow.com/questions/3438833/what-classes-should-i-use-in-my-project-what-else-should-i-know-about-oop – good_evening Sep 13 '10 at 18:50

score 0 · Answer 2 · answered Sep 13 '10 at 16:31

0

Your question is kind of confusing, but I think you are looking for this.

SELECT * FROM forum_topics WHERE cat_id IN(1,2,3,4,5,6,7) group by cat_id order by max(last_message) LIMIT 7

answered Sep 13 '10 at 16:31

davydotcom

2,170
1
16
18

score 0 · Answer 3 · answered Sep 13 '10 at 16:32

0

add a group by clause like GROUP BY cat_id

So your query will become SELECT * FROM forum_topics WHERE cat_id IN(1,2,3,4,5,6,7) GROUP BY cat_id ORDER BY last_message DESC LIMIT 7

answered Sep 13 '10 at 16:32

Faisal Feroz

12,458
4
40
51

How to make this Mysql query work?

3 Answers3