CODEWITHSUNDEEP
phpmysql
MajAfy
MajAfy

Reputation: 3097

Select from database order by count of records

I have a table like this:

Tags:

id | tag   | news_id
---+-------+---------
1  | tag1  | 1
2  | tag2  | 1
3  | tag3  | 1
4  | tag1  | 2
5  | tag2  | 2
6  | tag3  | 3
7  | tag1  | 3
8  | tag3  | 4
9  | tag1  | 5

How can I select tag field but order by count of tags ? I mean an output like this:

tag1,tag3,tag2

Upvotes: 0

Views: 79

Answers (2)

BabyDuck
BabyDuck

Reputation: 1249

To get your desired output, use GROUP_CONCAT() along with GROUP BY to concatenate rows by comma. Like this, 

SELECT GROUP_CONCAT(t.tag SEPARATOR ',') AS str_tags
FROM
(
   SELECT tag
   FROM tags
   GROUP BY tag
   ORDER BY COUNT(tag) DESC
) AS t;

Where the sub query returns the output like this:

 tag
-----
 tag1
 tag3
 tag2

And GROUP_CONCAT() in outer query, concatenates the resulting rows by comma:

tag1,tag3,tag2

Hope it helps, thanks.

Upvotes: 1

Javaluca
Javaluca

Reputation: 857

SELECT t.tag
FROM table t
GROUP BY t.tag
ORDER BY COUNT(t.tag) desc

Upvotes: 6

Related Questions