?
Solved

Count and Cull at the same time

Posted on 2011-10-18
5
Medium Priority
?
264 Views
Last Modified: 2012-05-12
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?

0
Comment
Question by:P19_3141
  • 2
4 Comments
 
LVL 6

Expert Comment

by:Molly Fagan
ID: 36986246
Use HAVING instead.

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)
0
 
LVL 41

Accepted Solution

by:
ralmada earned 1000 total points
ID: 36986428
try

select * from (
      SELECT
           ROW_NUMBER() OVER(PARTITION BY NText ORDER BY NText) AS CNT
           ,NText
      FROM CMV
      WHERE NText IS NOT NULL
) as a
WHERE CNT>1
0
 

Author Closing Comment

by:P19_3141
ID: 36986512
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.
0
 
LVL 6

Expert Comment

by:Molly Fagan
ID: 36986532
Oops, I should have put:

HAVING ROW_NUMBER() OVER(PARTITION BY NText ORDER BY NText) > 1

But glad you got your problem solved:-)
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
Suggested Courses

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question