Prev: delete rows from a large table with text column
Next: Not allow multiples in same month
From: Tom Cooper on 3 Mar 2010 17:04
My fault. I definitely needed another cup of coffee this morning.

Should have been

Select p.cod, p.loc, p.description
From Products p
Inner Join (Select p1.cod, Count(Distinct p1.description) As DistinctCount
From Products p1
Group By p1.cod
Having Count(Distinct p1.description) > 1) As x On p.cod = x.cod;

Tom

"Bruno" <brunobl(a)hotmail.com> wrote in message
news:hffto5purl13cgb0d12s42hhcc0bkvm7db(a)4ax.com...
> Tom,
>
> First, thanks so much for putting your time into this.
>
>>One way
>>
>>Select p.cod, p.loc, p.description
>>From Products p
>>Inner Join (Select p1.cod, Count(Distinct p1.description
>> From Products p1
>> Group By p1.cod
>> Having Count(Distinct p1.description) > 1);
>
> I can't get that to run. I see there is a parethesis mismatch
> somewhere but cannot for the life of me find the right configuration.
>
> I'd be VERY grateful if you could spare yet some more time and help me
> out trying to get this example to run.
>
> Thanks for the normalizing heads-up. This is a cross-platform, 24yrs+
> old system with clipper/DBF & SQL ends. Pretty interesting stuff.
>
> Thanks again,
> Bruno.

From: Plamen Ratchev on 3 Mar 2010 17:07
Not sure I understand, but try this:

SELECT code, loc, description
FROM (
SELECT code, loc, description,
COUNT(*) OVER (PARTITION BY code, description) AS cnt,
COUNT(*) OVER (PARTITION BY code) AS cnt2
FROM Codes) AS C
WHERE cnt <> cnt2;

--
Plamen Ratchev
http://www.SQLStudio.com
First  |  Prev  | 
Pages: 1 2
Prev: delete rows from a large table with text column
Next: Not allow multiples in same month