CODEWITHSUNDEEP
mysql
Abi
Abi

Reputation: 337

MySQL - remove non-duplicate rows from query

I'm running a query that selects details of orders, and I want to see only the orders that have gone through multiple stages. My data looks like:

id | order_id | action
1    100        1
2    100        2
3    100        4
4    101        1
5    102        2
6    103        1
7    103        2

So that only the rows for order_id 100 and 103 will be selected. This needs to be nested in a larger query.

Upvotes: 1

Views: 131

Answers (3)

Gouda Elalfy
Gouda Elalfy

Reputation: 7023

you can try this query:

select * from your_table 
where ( select count(*) from your_table  internal_table
 where your_table .order_id = internal_table.order_id

) > 1

Upvotes: 0

Abhishek Sharma
Abhishek Sharma

Reputation: 6661

Use group by with count and having

select *,count(order_id) as total from table 
group by order_id
having total > 1

Upvotes: 0

fthiella
fthiella

Reputation: 49049

You can use a subquery to get the orders that had multiple stages:

SELECT order_id
FROM your_table
GROUP BY order_id
HAVING COUNT(*)>1

then you can join this result back to your table:

SELECT o.*
FROM yourtable AS o INNER JOIN (
    SELECT order_id
    FROM your_table
    GROUP BY order_id
    HAVING COUNT(*)>1
  ) dup ON o.order_id = dup.order_id

Upvotes: 2

Related Questions