Find max number of times a tag is used

4DownUp

I have a HABTM tags/post schema.

The data in the table posts_tags table looks like.


post_id     tag_id
1           10
2           11
3           11
3           12
4           11
5           13

How can I get the highest number a tag is used? In my example data It would be the tag with Id 11 and the value would be "3".

Asked by powtac, on 13/11/09

2 Answers

6DownUp

Try this out for:

SELECT count(tag_id) AS hightest_number FROM diagrams_tags GROUP BY tag_id ORDER BY hightest_number DESC LIMIT 1


$this->diagramTag->find('first', array(
  'fields'=>array('count(tag_id) as highest_number'),
  'group'=>'tag_id',
  'order'=>'highest_number desc'
));

Answered by jimiyashon 13/11/09

This is an excellent answer, but take care how returned data is formed as it may not follow cake conventions.

hazeim - on 22/12/09

<< comments | comments >>
0DownUp

I haven't tried this, but try to use a SQL query like the following:


SELECT tag_id, count(tag_id) FROM posts_tags;

Answered by janbon 13/11/09 


SELECT count(tag_id) AS hightest_number FROM diagrams_tags GROUP BY tag_id ORDER BY hightest_number DESC LIMIT 1

This does the job!!! But is there a more cake like way?

powtac - on 13/11/09

<< comments | 1 | 2
<< previous next >>

Tagged with

Rating

4

Viewed

2484 times

Last Activity

on 22/12/09

Related Questions