From: | Carol Walter <walterc(at)indiana(dot)edu> |
---|---|
To: | postgres-novice(at)coreland(dot)ath(dot)cx |
Cc: | pgsql-novice(at)postgresql(dot)org |
Subject: | Re: Remove duplicate rows and order by number of occurences |
Date: | 2009-01-29 18:57:28 |
Message-ID: | 3B690521-02FB-4078-BCFD-4F0D850146B0@indiana.edu |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
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(at)coreland(dot)ath(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-novice
From | Date | Subject | |
---|---|---|---|
Next Message | A B | 2009-01-29 21:16:12 | How to log to postgresql.log |
Previous Message | Mike Ellsworth | 2009-01-29 17:27:09 | Re: Remove duplicate rows and order by number of occurences |