How to get rows from a SQL table just ignoring the rows which have duplicate values in one particula-CodePudding

I know that if I want to have distict values in case of duplicates, I can use DISTINCT. But what I want is that if there is a duplicate value in one of the columns, I don't need any row from that .

eg.

ORDER_ID	NAME
ORD1	Aaron
ORD2	BOB
ORD3	Carry
ORD1	Danny
ORD2	Emily
ORD2	Frank

In this case, I just want the result as:

ORDER_ID	NAME
ORD3	Carry

Since ORD1 and ORD3 are repeated.

CodePudding user response：

You can use an aggregation with GROUP BY and specify a condition on the groups with HAVING:

SELECT * FROM tbl
GROUP BY order_id
HAVING count(*) = 1;

CodePudding user response：

Best I can think of is:

SELECT * FROM tbl
WHERE name NOT IN (SELECT name FROM tbl GROUP BY name HAVING count(name) > 1) AND order_id NOT IN (SELECT order_id FROM tbl GROUP BY order_id HAVING count(order_id) > 1)

CodePudding user response：

NOT EXISTS is a typical approach:

select t.*
from t
where not exists (select 1 from t t2 where t2.order_id = t.order_id and t2.name <> t.name);

CodePudding user response：

Hello i tried this and it worked for me.

SELECT Order_Id, MAX(Name) as Name FROM table GROUP BY Order_Id HAVING COUNT(*) = 1;