Postgres Pro
Downloads
Home   >   mailing lists

Thread: Remove duplicate rows and order by number of occurences

Remove duplicate rows and order by number of occurences

From
postgres-novice@coreland.ath.cx
Date:
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.

Re: Remove duplicate rows and order by number of occurences

From
"Oliveiros Cristina"
Date:
Try this

  SELECT file_id ,COUNT(*) as numberOfOccurrences
FROM temp_tagged
GROUP BY file_id
ORDER BY numberOfOccurrences DESC;

These kind of questions are better answered in this mailing list :
pgsql-sql@postgresql.org


Best,
Oliveiros

----- Original Message -----
From: <postgres-novice@coreland.ath.cx>
To: <pgsql-novice@postgresql.org>
Sent: Thursday, January 29, 2009 5:04 PM
Subject: [NOVICE] Remove duplicate rows and order by number of occurences


> 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.
>
> --
> Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-novice
>

Re: Remove duplicate rows and order by number of occurences

From
Mike Ellsworth
Date:
> 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.
>

Try
SELECT file_id, count(file_id) FROM temp_tagged GROUP BY file_id ORDER
BY count(file_id) DESC

Re: Remove duplicate rows and order by number of occurences

From
Carol Walter
Date:
Hello,

I think you need something like ...

SELECT count(*), file_id
     FROM temp_tagged
     GROUP BY file_id
     HAVING count(*) > 1;

This will select only those records that are duplicated.

Carol

On Jan 29, 2009, at 12:04 PM, postgres-novice@coreland.ath.cx wrote:

> 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.
>
> --
> Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-novice