Query to Return 1 Instance of a Record with Duplicates

Query to return 1 instance of a record with duplicates

Have you tried this?

SELECT id, value, MIN(Signal), MIN(Read), MIN(Firmware), MIN(Date), MIN(Time)
FROM
  ...
GROUP BY
  ID, Value

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.

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

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.

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

Show First instance of records with duplicate values in colum

Some additional to John Woo's solution:

SELECT  a.*
FROM    gallery_images a
    INNER JOIN
    (
        SELECT gallery_ID, MIN(id) id
        FROM gallery_images 
        GROUP BY gallery_ID
    ) b
        ON a.gallery_ID = b.gallery_ID AND
            a.id = b.id

we must get by minimum record id's not by min(image_id) cause image_id can be coming in different order.

Finding duplicate values in a SQL table

SELECT
    name, email, COUNT(*)
FROM
    users
GROUP BY
    name, email
HAVING 
    COUNT(*) > 1

Simply group on both of the columns.

Note: the older ANSI standard is to have all non-aggregated columns in the GROUP BY but this has changed with the idea of "functional dependency":

In relational database theory, a functional dependency is a constraint between two sets of attributes in a relation from a database. In other words, functional dependency is a constraint that describes the relationship between attributes in a relation.

Support is not consistent:

• Recent PostgreSQL supports it.
• SQL Server (as at SQL Server 2017) still requires all non-aggregated columns in the GROUP BY.
• MySQL is unpredictable and you need sql_mode=only_full_group_by:
  
  
  • GROUP BY lname ORDER BY showing wrong results;
  • Which is the least expensive aggregate function in the absence of ANY() (see comments in accepted answer).
• Oracle isn't mainstream enough (warning: humour, I don't know about Oracle).

Finding duplicate values in MySQL

Do a SELECT with a GROUP BY clause. Let's say name is the column you want to find duplicates in:

SELECT name, COUNT(*) c FROM table GROUP BY name HAVING c > 1;

This will return a result with the name value in the first column, and a count of how many times that value appears in the second.

---

Related Topics