CODEWITHSUNDEEP
sqlsql-server
Shmewnix
Shmewnix

Reputation: 1573

How to display duplicate value in a specific field

I have an sql query that i'd like to display only those records where a specific field has double entries.

Here is an example of my current output:

Test 1        Test2         Test3
1               12            654
2               12            4655
3               15            65987
4               16            3548
5               14            7348
6               14            365
7               17            987
8               L13           6547
9               L13           98687

I'd like to pull only the following (only those where Test2 has a count of > 1 and is numeric):

Test 1        Test2         Test3
1               12            654
2               12            4655
5               14            7348
6               14            365

My current query is:

SELECT        *
FROM            Test
WHERE        (Test4 BETWEEN @startdate AND @enddate) AND (isnumeric(test2) = 1)
Group by Test1
having Count(Distinct(test2)) > 1
ORDER BY Test2, test3

Upvotes: 1

Views: 55

Answers (2)

Peter Krassoi
Peter Krassoi

Reputation: 567

Use a subquery for count and selection:

Select * From test
where test2 in (
    Select test2 From (
        Select test2, count(*) C from test
        where (Test4 BETWEEN @startdate AND @enddate) AND (isnumeric(test2) = 1)
        group by test2 
        having count(*) > 1 ) f
    )

Upvotes: 2

Lamak
Lamak

Reputation: 70638

;WITH CTE AS
(
    SELECT  *,
            N = COUNT(*) OVER(PARTITION BY Test2)
    FROM YourTable
    WHERE ISNUMERIC(Test2) = 1
)
SELECT *
FROM CTE
WHERE N > 1;

Upvotes: 0

Related Questions