get records on the basis of their occurences

[ayushchd]

Hi All.

Suppose I have in a table :

id : msg_id
1 : 1
2 : 2
3 : 1
4 : 1
5 : 3
6 : 2
7 : 3
8 : 3
9 : 1
10 : 1

A query that will give me the msg_ids in decreasing order of their number of occurrences.
For above example, it should give :
1, 3 and 2

[phranque]

SELECT DISTINCT(msg_id) FROM msg_table_name GROUP BY msg_id ORDER BY COUNT (msg_id) DESC

[ayushchd]

it says invalid use of group function

[phranque]

perhaps it is unnecessary.
try this:
SELECT DISTINCT(msg_id) FROM msg_table_name ORDER BY COUNT(msg_id) DESC

[ayushchd]

still says :
#1111 - Invalid use of group function

[ayushchd]

This Worked!

SELECT DISTINCT (msg_id), COUNT( msg_id ) AS msgcount FROM discussions
GROUP BY msg_id ORDER BY msgcount DESC

[ZydoSEO]

The GROUP BY should make it distinct... So a slightly simplified version of the above should be:

SELECT msg_id, COUNT(msg_id) AS msgcount
FROM discussions
GROUP BY msg_id
ORDER BY msgcount DESC

Haven't tried it but this should also work.