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)

And would like to instead retreive:

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!

CodePudding user response：

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;

CodePudding user response：

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)

CodePudding user response：

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