SQL Query for non duplicate items only

user_19780963 0
Viewed 55

I'm attempting to build a query to only retrieve only unique records on a table. I would like to pull all info from the table using Select *.

For example I would like to do Select * from Bin, and only show records that DO NOT have a duplicate value in 1 specific column.

Example of result for Select * from Bin (it returns a lot more columns, but you get the idea)

Station MyNo Item Qty Max Min
77 1-A 1234 10 10 5
77 1-B 1234 2 4 1
77 1-C 5432 5 8 2
77 1-D 2851 18 20 8
77 1-E 9432 24 25 15

And would like to instead retreive:

Station MyNo Item Qty Max Min
77 1-C 5432 5 8 2
77 1-D 2851 18 20 8
77 1-E 9432 24 25 15

SELECT DISTINCT will not work, as I do not want to retrieve anything that has a duplicate value in the "Item" field. I'm not sure if HAVING COUNT would work, but have not been able to get HAVING COUNT to work for me.

I hope this makes sense with how I explained it. I'm currently using SQL Server Management Studio 18. I am fairly new to SQL so I appreciate any help I can get.

Thanks for the help!

sql
3 Answers

We can use the COUNT() function here as a window function:

WITH cte AS (
    SELECT *, COUNT(*) OVER (PARTITION BY Item) cnt
    FROM Bin
)

SELECT Station, MyNo, Item, Qty, Max, Min
FROM cte
WHERE cnt = 1;
user_18632290

A subquery containing those duplicate records is another option to solve this problem.

select * from Bin
where item not in 
    (select item from Bin group by item having count(*) > 1)
user_64290720

Try this simple query to show all column with items not in duplicates value

select item ,STRING_AGG([Station], ',') as [Station] ,STRING_AGG([MyNo], ',') as [MyNo] ,STRING_AGG([Qty], ',') as [Qty] ,STRING_AGG([Max], ',') as [Max]  ,STRING_AGG([Min], ',') as [Min] from Bin group by item  having count(*) = 1
user_142918090
Related