Display only records which are not duplicated

I wish to display only records which have no duplicate.

CREATE TABLE #TempTbl1 
(
    Circuit VARCHAR(55),
    Bandwidth DECIMAL(7,1),
    Unit BIT,
    CurrentID INT,
);

INSERT INTO #TempTbl1
VALUES
    ('28.L9XX.100277..COXC.', 5.0, 0, 3364),
    ('28/LEHZ/010421/121/CFOK', 100.0, 0, 3223),
    ('#2009-191:604', 10.0, 0, 3100),
    ('28.L9XX.100277..COXC.', 100.0, 0, 3364),
    ('#2009-191:604', 150.0, 0, 3100),
    ('00/GIGE/M3283961/395/303/395', 200.0, 1, 4500)

I have the following result

 Circuit                       Bandwidth    Unit    CurrentID
 ------------------------------------------------------------
 28.L9XX.100277..COXC.          5.0         0       3364
 28/LEHZ/010421/121/CFOK        100.0       0       3223
 #2009-191:604                  10.0        0       3100
 28.L9XX.100277..COXC.          100.0       0       3364
 #2009-191:604                  150.0       0       3100
 00/GIGE/M3283961/395/303/395   200.0       1       4500

I would like my result to look like the following. As you can see I only want to display Circuits which occur only once.

 Circuit                       Bandwidth    Unit    CurrentID
 --------------------------------------------------------------
 28/LEHZ/010421/121/CFOK        100.0       0       3223
 00/GIGE/M3283961/395/303/395   200.0       1       4500

If you want only the unique circuits then group by circuit and set the condition in the HAVING clause:

select circuit, 
  max(bandwidth) bandwidth, 
  max(cast(unit as integer)) unit, 
  max(cast(currentid as integer)) currentid
from #TempTbl1
group by circuit
having count(*) = 1

The aggregation with MAX() in the select list will return the column values as there is only 1 value for each circuit. See the demo. Results:

> circuit                      | bandwidth | unit | currentid
> :--------------------------- | :-------- | ---: | --------:
> 00/GIGE/M3283961/395/303/395 | 200.0     |    1 |      4500
> 28/LEHZ/010421/121/CFOK      | 100.0     |    0 |      3223

SQL DISTINCT: Removing Duplicates In a Result Set Examples, The primary key ensures that the table has no duplicate rows. As you see we have duplicate salary data e.g., 17,000 because two or more employees As a result, the DISTINCT operator keeps only one NULL value and removes the other � In this case, i think the solution above is the better to display the elements. if you prefer, you can work with a list variable with a structure that you need to display in the screen, and at the preparation pass the records to list variable, removing the duplicateds, and use this variable like a source to list widget on the screen.

Try this:

SELECT *
FROM yourtable t1
WHERE 
   (SELECT count(1)
    FROM yourtable t2
    WHERE t2.circuit = t1.circuit
    AND t2.currentID = t1.currentID) = 1

Filter for unique values or remove duplicate values, Filter for unique values, remove duplicate values, and conditionally format unique Insert or delete rows, and columns Article � Hide or show rows or columns 74 records returned. Exercise: Find suppliers who supply more than 4 products. Here is the result of finding suppliers who sell more than 4 products to Northwind Trader. Make the following change in query 1 above. having count(*) > 4 10 records returned. Query 2: Find non-duplicated values. Find suppliers who only sell one product to Northwind

For a uniqueness per CurrentID & Circuit?

Then a COUNT OVER them should be 1.

;WITH CTE_CIRCUITS AS
(
    SELECT *
    , COUNT(*) OVER (PARTITION BY CurrentID, Circuit) AS Cnt
    FROM #TempTbl1
)
SELECT Circuit, Bandwidth, Unit, CurrentID
FROM CTE_CIRCUITS
WHERE Cnt = 1

How to Find Duplicate Values in a SQL Table, Now, we want to return the entire record for see that this query is not so complicated. DataFrame.duplicated(subset=None, keep='first') It returns a Boolean Series with True value for each duplicated row. Arguments: subset : Single or multiple column labels which should used for duplication check. If not provides all columns will be checked for finding duplicate rows. keep : Denotes the occurrence which should be marked as duplicate.

You can use the following code if we use currentid as a unique field.

WITH CTE
 AS (SELECT circuit, 
            FIRST_VALUE(bandwidth) OVER(PARTITION BY currentid
            ORDER BY currentid) bandwidth, 
            FIRST_VALUE(unit) OVER(PARTITION BY currentid
            ORDER BY currentid) unit, 
            currentid, 
            COUNT(*) OVER(PARTITION BY currentid
            ORDER BY currentid) CNT
     FROM #TempTbl1)
 SELECT circuit, 
        bandwidth, 
        unit, 
        currentid
 FROM CTE
 WHERE CNT = 1;

OR:

SELECT circuit, 
       bandwidth, 
       unit, 
       currentid
FROM #TempTbl1
WHERE currentid NOT IN
(
    SELECT currentid
    FROM #TempTbl1
    GROUP BY currentid
    HAVING COUNT(currentid) > 1
);

How To Find Duplicate Values in MySQL, This tutorial shows you step by step how to find duplicate values in one or more NULL, last_name VARCHAR(50) NOT NULL, email VARCHAR(255) NOT NULL ); Rows are considered duplicate only when the combination of columns are� When you remove duplicate values, the only effect is on the values in the range of cells or table. Other values outside the range of cells or table will not change or move. When duplicates are removed, the first occurrence of the value in the list is kept, but other identical values are deleted.

Why are there duplicates in the query results and how to minimize , Query is a way to group records; it is not a reporting tool. the query to filter Export, Reports, Mail, etc.; each record will only display once when used as a filter. I got a requirement where I should have the report displays ONLY the duplicated rows Fields are: Patient_ID, Patient_Name, Service_Code, Date_Of_Service, Provider_Name, Guarantor_Code, Total_Charge_Amount * So the manager wants to look at ONLY the duplicated rows in order to find out patients whom claimed the service more than once*

Finding Duplicates with SQL, Also see How to remove duplicate rows from a table - Microsoft Knowledge base What is the the proper SQL query to not only find the dupes but acutally� The layout that we see in Excel is WYSIWYG. In Tableau the default behavior is to aggregate the underlying records (rows) based on the dimensions (columns) in the view. The data set as-is does not have sufficient dimensionality (granularity) to uniquely identify each unique record that you want to display.

How to Use Excel to Show Only Duplicate Values, Select the rows and columns that you want to search for duplicate values. You can select multiple non-contiguous values throughout the spreadsheet by� pandas.DataFrame.duplicated¶ DataFrame.duplicated (subset = None, keep = 'first') [source] ¶ Return boolean Series denoting duplicate rows. Considering certain columns is optional. Parameters subset column label or sequence of labels, optional. Only consider certain columns for identifying duplicates, by default use all of the columns.

Comments
  • What happened to #2009-191:604? And 28.L9XX.100277..COXC.?
  • Please explain the logic you want to implement.
  • Your expected results shows 2 circuits, your data shows 4 unique circuits. Can you please clarify.
  • @DaleK #2009-191:604? And 28.L9XX.100277..COXC shows up twice with a different bandwidth the original table. I would like to query these results but only display 28/LEHZ/010421/121/CFOK and 00/GIGE/M3283961/395/303/395 because it only shows up once. Does that help? You guys are very quick in answering. Unfortunately, My coding example to display here still needs work. I apologize for my confusion.
  • Most people read "not display duplicates" as when there is a duplicate to only return 1 row instead of multiple. In your case it seems when there is a duplicate you don't want any row for that circuit.