How to Return Only 1 Row If Multiple Duplicate Rows and Still Return Rows That Are Not Duplicates

Select Only Non Duplicated Rows (SQL)

You can use count() as a window function:

Select t1.Id, t1.Value, t2.Id, t2.Value
From Table_1 Join
     (select t2.*,
             count(*) over (partition by t2.value) as cnt
      from Table_2 t2
     ) t2
     on t2.Value = t1.Value
where t2.cnt = 1;

Based on your sample data, left join does not seem appropriate.

Left Join without duplicate rows from left table

Try an OUTER APPLY

SELECT 
    C.Content_ID,
    C.Content_Title,
    C.Content_DatePublished,
    M.Media_Id
FROM 
    tbl_Contents C
    OUTER APPLY
    (
        SELECT TOP 1 *
        FROM tbl_Media M 
        WHERE M.Content_Id = C.Content_Id 
    ) m
ORDER BY 
    C.Content_DatePublished ASC

Alternatively, you could GROUP BY the results

SELECT 
    C.Content_ID,
    C.Content_Title,
    C.Content_DatePublished,
    M.Media_Id
FROM 
    tbl_Contents C
    LEFT OUTER JOIN tbl_Media M ON M.Content_Id = C.Content_Id 
GROUP BY
    C.Content_ID,
    C.Content_Title,
    C.Content_DatePublished,
    M.Media_Id
ORDER BY
    C.Content_DatePublished ASC

The OUTER APPLY selects a single row (or none) that matches each row from the left table.

The GROUP BY performs the entire join, but then collapses the final result rows on the provided columns.

Selecting one row from duplicated rows

SELECT  DISTINCT DocID, VisitDate, DocName
FROM    mytable

Or I am missing something?

Update:

If you have control over the stored procedure, you can rewrite it so that no duplicates will ever get into the table.

Assuming DocID is a PRIMARY KEY, declare it as such in the temporary table using IGNORE_DUP_KEY:

DECLARE @temp TABLE (DocId INT NOT NULL PRIMARY KEY WITH (IGNORE_DUP_KEY = ON), …)

INSERT
INTO    @mytable
SELECT  …
FROM    source_table

This will skip duplicates on DocID

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.

Select one row without duplicate entries

It's pretty simple:

SELECT DISTINCT name FROM info WHERE status = 1 ORDER BY id

The SQL keyword DISTINCT does the trick.

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).

get 1 row from multiple rows MYSQL

Sounds like you need to use GROUP BY:

select a.instalmentnbr, a.duedate, sum(a.capital_payment), sum(a.interest_payment), sum(a.overdue_payment)
from helltable a
where a.request_orig_id = 46  
group by a.instalmentnbr, a.duedate
order by a.instalmentnbr;

---

Related Topics