SQL Return Only Duplicate Rows

SQL Return only duplicate rows

First, identify the duplicates. Second, join back to extract these rows.

A non-aggregated (or non-window/ranking) self join forms a partial cross join and gives the square of duplicates for any set of keys. Including non-duplicates too. 1 x 1 = 1 after all.

SELECT
    t2.*
FROM
    (
    SELECT 
       StateId, OrderId, OrderTime, PermitId
    FROM
       myTable
    GROUP BY
       StateId, OrderId, OrderTime, PermitId
    HAVING
       COUNT(*) >= 2
    ) T1
    JOIN
    mytable T2 ON T1.StateId = T2.StateId AND T1.OrderId = T2.OrderId AND
                   T1.OrderTime = T2.OrderTime AND T1.PermitId = T2.PermitId

Return only duplicate values from table

Here is one method:

select bnumber
from t
where anumber in (3217913664, 3006307180)
group by bnumber
having min(anumber) < max(anumber);

If the rows have no duplicates, then using count(*) = 2 is an alternative.

If you have more than 2 anumbers that you want to test, then use count(distinct anumber) = n, where n is the number of values in the in list.

How to select only duplicate records?

Select State FROM Area
GROUP BY State
Having COUNT(*) > 1

Try this

Update the query with your own column and table names

sql select only duplicate rows

One method is exists:

select distinct username, ip
from t
where exists (select 1 from t t2 where t2.username = t.username and t2.ip <> t.ip);

How to pick first record from the duplicates, With only duplicate column values

You Can use ROW_NUMBER function to assign row numbers to each of your records in the table.

select *
from(    
select *, ROW_NUMBER() OVER(PARTITION BY t.id) rn
    from t)
Where rn = 1

ROW_NUMBER does not require the ORDER BY clause. Returns the sequential row ordinal (1-based) of each row for each ordered partition. If the ORDER BY clause is unspecified then the result is non-deterministic.
If you have record created date or modified dates you can use those in the ORDER BY clause to alway pick up the latest records.

SQL Select only duplicates from one column

select t.bn, t.system, sum(t.amount)
  from tbl1 t
  join (select bn from tbl1 group by bn having count(distinct system) > 1) x
    on t.bn = x.bn
 group by t.bn, t.system

I think by 'duplicate' you mean the same BN value is associated with 2+ unique systems?

The above should work if that is the case.

---

Related Topics