CODEWITHSUNDEEP
sqlsql-serverselectduplicates
FredTheDog
FredTheDog

Reputation: 63

Find Duplicates in table

I have a table called FLOORS which contains a many duplicate values

FLOORCODE   FLOORDESC
00          Ground Floor
01          1st Floor
02          2nd Floor
00          Ground Floor
00          NEW Floor

What code do I need for a query that will only bring back values where the same FLOORCODE has been used but with a different FLOORDESC. So in the above example I only want to see

FLOORCODE     FLOORDESC
00            Ground Floor
00            NEW Floor

Upvotes: 1

Views: 89

Answers (5)

kevin dockerty
kevin dockerty

Reputation: 13

This might do it

select floorcode, floordesc
from yourtable where floorcode in 
( select floorcode 
  from yourtable
  group by floorcode having count(*) >1 )

Upvotes: 0

Mureinik
Mureinik

Reputation: 311308

You can use a having clause to find the offending floorcode, and then an in clause to find the actual rows:

SELECT DISTINCT floorcode, floordesc
FROM   floors
WHERE  floorcode IN (SELECT   floorcode
                     FROM     floors
                     GROUP BY floorcode
                     HAVING   COUNT(DISTINCT floordesc) > 1)

Upvotes: 0

Evaldas Buinauskas
Evaldas Buinauskas

Reputation: 14077

This could be done using windowed count as well:

DECLARE @Test TABLE
(
    FLOORCODE VARCHAR(50),
    FLOORDESC VARCHAR(50)
);

INSERT INTO @Test (FLOORCODE, FLOORDESC)
VALUES ('00', 'Ground Floor')
    , ('01', '1st Floor')
    , ('02', '2nd Floor')
    , ('00', 'Ground Floor')
    , ('00', 'NEW Floor');

SELECT DISTINCT FLOORCODE, FLOORDESC
FROM (
    SELECT FLOORCODE, FLOORDESC, COUNT(*) OVER(PARTITION BY FLOORCODE) AS Cnt
    FROM @Test
    ) AS T
WHERE T.Cnt > 1;

Result:

╔═══════════╦══════════════╗
║ FLOORCODE ║  FLOORDESC   ║
╠═══════════╬══════════════╣
║        00 ║ Ground Floor ║
║        00 ║ NEW Floor    ║
╚═══════════╩══════════════╝

Upvotes: 0

Devart
Devart

Reputation: 121922

DECLARE @t TABLE (
    FLOORCODE CHAR(2),
    FLOORDESC VARCHAR(50)
)

INSERT INTO @t
VALUES
    ('00', 'Ground Floor'),
    ('01', '1st Floor'),
    ('02', '2nd Floor'),
    ('00', 'Ground Floor'),
    ('00', 'NEW Floor')

SELECT DISTINCT t2.FLOORCODE, t2.FLOORDESC
FROM (
    SELECT FLOORCODE
    FROM @t
    GROUP BY FLOORCODE
    HAVING COUNT(DISTINCT FLOORDESC) > 1
) t
JOIN @t t2 ON t.FLOORCODE = t2.FLOORCODE

result - 

FLOORCODE FLOORDESC
--------- ----------------
00        Ground Floor
00        NEW Floor

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1269763

A simple way to do this is using window functions:

select f.*
from (select f.*, min(floordesc) over (partition by floorcode) as minfd,
             max(floordesc) over (partition by floorcode) as maxfd
      from floors f
     ) f
where minfd <> maxfd;

An alternative that doesn't use window functions might use exists:

select f.*
from floors f
where exists (select 1
              from floors f2
              where f2.floorcode = f.floorcode and f2.floordesc <> f.floordesc
             );

Note that both of these can benefit from an index on floors(floorcode, floordesc). And both these versions ignore NULL values, although that is easily incorporated into the logic.

Upvotes: 1

Related Questions