P19_3141
asked on
Count and Cull at the same time
Is there a way to count duplicates (cnt), and in the same run limit the output to only instances where cnt>1?
SELECT
ROW_NUMBER() OVER(PARTITION BY NText ORDER BY NText) AS CNT
,NText
FROM CMV
WHERE NText IS NOT NULL
AND CNT>1
In this example, since the CNT is in SELECT I can't use it to cull in the WHERE. This example is the gist fo what I'm trying to do, how can I order this statement better?
SELECT
ROW_NUMBER() OVER(PARTITION BY NText ORDER BY NText) AS CNT
,NText
FROM CMV
WHERE NText IS NOT NULL
AND CNT>1
In this example, since the CNT is in SELECT I can't use it to cull in the WHERE. This example is the gist fo what I'm trying to do, how can I order this statement better?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Having solution did not work. Gave error can't use "window function" in Having statement.
The second, or ") as a WHERE CNT>1" solution worked for me.
The second, or ") as a WHERE CNT>1" solution worked for me.
Oops, I should have put:
HAVING ROW_NUMBER() OVER(PARTITION BY NText ORDER BY NText) > 1
But glad you got your problem solved:-)
HAVING ROW_NUMBER() OVER(PARTITION BY NText ORDER BY NText) > 1
But glad you got your problem solved:-)
SELECT
ROW_NUMBER() OVER(PARTITION BY NText ORDER BY NText) AS CNT
,NText
FROM CMV
WHERE NText IS NOT NULL
GROUP BY NText
HAVING ROW_NUMBER() OVER(PARTITION BY NText ORDER BY NText)