get records on the basis of their occurences

Forum Moderators: open

Message Too Old, No Replies

10:33 am on Nov 24, 2007 (gmt 0)

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

11:34 am on Nov 24, 2007 (gmt 0)

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

11:51 am on Nov 24, 2007 (gmt 0)

it says invalid use of group function

12:01 pm on Nov 24, 2007 (gmt 0)

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

2:37 pm on Nov 24, 2007 (gmt 0)

still says :
#1111 - Invalid use of group function

3:00 pm on Nov 24, 2007 (gmt 0)

This Worked!

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

7:39 pm on Nov 24, 2007 (gmt 0)

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.