Remove duplicate rows and order by number of occurences

From: postgres-novice(at)coreland(dot)ath(dot)cx
To: pgsql-novice(at)postgresql(dot)org
Subject: Remove duplicate rows and order by number of occurences
Date: 2009-01-29 17:04:06
Message-ID: 20090129170406.GB93056@logik.internal.network
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

Hello.

I'm trying to SELECT from the following table:

file_id
---------
1
1
2
3
5
6
9
9
9
10
10

The result I'm trying to achieve is essentially to order the
file_id column, in descending order, by the number of times
each row occurs with a given value. I would also like to remove
duplicate rows. In other words, the above table becomes
(approximately):

file_id
---------
9
10
1
6
5
3
2

The order of the last four values isn't significant (they occur
an equal number of times).

I thought that this might be the solution:

SELECT file_id FROM temp_tagged;
GROUP BY file_id
ORDER BY count (file_id) DESC;

But apparently, it isn't.

Any help would be appreciated.

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Oliveiros Cristina 2009-01-29 17:21:08 Re: Remove duplicate rows and order by number of occurences
Previous Message Carol Walter 2009-01-29 16:13:11 Re: REALLY stupid question