Selecting Only One Duplicate Record

Get top first record from duplicate records having no unique identity

The answer depends on specifically what you mean by the "top 1000 distinct" records.

If you mean that you want to return at most 1000 distinct records, regardless of how many duplicates are in the table, then write this:

SELECT DISTINCT TOP 1000 id, uname, tel
FROM Users
ORDER BY <sort_columns>

If you only want to search the first 1000 rows in the table, and potentially return much fewer than 1000 distinct rows, then you would write it with a subquery or CTE, like this:

SELECT DISTINCT *
FROM
(
    SELECT TOP 1000 id, uname, tel
    FROM Users
    ORDER BY <sort_columns>
) u

The ORDER BY is of course optional if you don't care about which records you return.

How to get only one record for each duplicate rows of the id in oracle?

There are no standard aggregate functions in Oracle that would work with BLOBs, so GROUP BY solutions won't work.

Try this one based on ROW_NUMBER() in a sub-query.

SELECT inn.group_id, inn.image, inn.image_id
FROM
(
    SELECT t.group_id, t.image, t.image_id, 
        ROW_NUMBER() OVER (PARTITION BY t.group_id ORDER BY t.image_id) num
    FROM theTable t
) inn
WHERE inn.num = 1;

The above should return the first (based on image_id) row for each group.

SQL Fiddle

SQL:How to select the first record from duplicate rows?

select distinct * 
from ( select a.*, count(*) over (partition by a.ID) as tot
       from HREMP a 
     ) tt
where tt.tot > 1

or

select * 
from ( select a.*
            , count(*)     over (partition by a.ID) as tot
            , row_number() over (partition by a.ID order by 1) as rn
       from HREMP a 
     ) tt
where tt.tot > 1 
and   tt.rn = 1

Select or update only one duplicate from multiple rows

You are part-way there, you need to use row_number over a window, then you can use an updatable CTE:

with d as (
    select * , Row_Number() over(partition by number order by id) rn
    from customers
)
update d set deleted=1 where rn>1

Select 1 record from each of 2 duplicate records

SQL Select distinct rows with duplicate values in one column and choose one row for each duplicate based on value in primary key field

Something like this should work:

select FacilityID, max(FacilityKey)
from Facilities
group by FacilityID
having count(FacilityID)>1

And then if you want to get all of the fields, something like this:

select *
from facilities
    inner join (
        select FacilityID, max(FacilityKey) as maxkey
        from Facilities
        group by FacilityID
        having count(FacilityID)>1
      ) t on t.FacilityID = facilities.FacilityID and t.maxkey=facilities.FacilityKey

---

Related Topics