Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-novice by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group