Re: help with simple sql
--- Carlos Benjamin <benj@firstlinux.net>
> wrote:
>
>
>--- "dios" <adiosadios@crosswinds.net>
>> wrote:
>>Hola guru's,
>>What is the correct informix syntax for selecting all rows where the one
>>column contains duplicates and the selection criteria is only on the one
>>column? Right now I am running comparisons against the orig and a temp
>>table.
>>
>>table:
>>column 1 col 2
>>ss01 01
>>ss02
>>ss01 02
>>ss03
>>ss01 03
>>ss04 01
>>ss04 02
>>ss05
>>
>>output:
>>col 1
>>ss01 01
>>ss01 02
>>ss01 03
>>ss04 01
>>ss04 02
>>
>>search criteria is col 1 only! ss03 and ss05 are not included because they
>>are unique
>
>Select col1, col2
>from table
>group by col1, col2
>order by col1, col2
>having count(col1) > 1>
>Should find only duplicate values for col1. (The order by clause is optional).
>
>carlos
Whoa! That won't work! What hare-brained noggin hatched that? Oh, I see now.....
Wasn't even thinking about the second column when I typed that.
Select col1, min(col2)
from table
group by col1orderby col1
having count(col1) > 1
That looks better.
carlos
==
Maintainer of the procrastinator's FAQ. Well, maybe tomorrow I will be.
_____________________________________________________________
Want a new web-based email account ? ---> http://www.firstlinux.net