|
|
Posted by Stefan Rybacki on 11/29/05 17:26
Hilarion wrote:
>...
> The select statement above will not work in many SQL engines. You should
> NOT use "*" in GROUP BY select statements. It should be something like
> this:
>
> SELECT product, description, dnote, COUNT(*) AS repeat_count
> FROM some_table
> GROUP BY product, description, dnote
> HAVING COUNT(*) > 1
>
> Why? Because SQL standards require that only fields retrieved by
> SELECT be from GROUP BY clause or be aggregate values.
Hi Hilarion,
I know this, of course, I just used * because it is easier and faster to write ;)
>
>
> I'm also assuming that Brian wants to remove duplicates, but leaving
> one entry. It can't be done without using some other field in this
> table which differs between the records. If there's no such field,
> then the only way is to remove all entries and then reinserting one.
Yes. This is a way.
>
> To Brian:
> If you have, for example, a "product_id" field in the table,
> then you can list all duplicate entries and also get one "product_id"
> value, that should not be removed. I assumed that "product_id"
> is an autoincrement and that you want to leave the oldest entry
> (which will have lowest value of "product_id"). In that case
> the query to list the entries would look like this:
>
> SELECT
> product,
> description,
> dnote,
> COUNT(*) AS repeat_count,
> MIN( product_id ) AS product_id_to_stay
> FROM some_table
> GROUP BY product, description, dnote
> HAVING COUNT(*) > 1
>
Another nice way ;)
>
>...
> Hilarion
Regards
Stefan
Navigation:
[Reply to this message]
|