CODEWITHSUNDEEP
mysqlsql
Ethan
Ethan

Reputation: 60099

How could I write a query to find duplicate values within grouped rows?

Table:

locations
---------
contract_id INT
order_position INT   # I want to find any bad data in this column

I'd like to find any duplicate order_positions within a given contract_id.

So for example any cases like if two locations rows had a contract_id of "24845" and both had an order_position of "3".

I'm using MySQL 5.1.

Upvotes: 0

Views: 122

Answers (4)

RandomSeed
RandomSeed

Reputation: 29759

SELECT contract_id, order_position, COUNT(order_position) AS cnt
FROM locations
GROUP BY contract_id, order_position
HAVING cnt > 1;

Why COUNT(column) is usually better than COUNT(*) (okay, this was just to differentiate my answer :D)

Upvotes: 1

Hart CO
Hart CO

Reputation: 34774

A HAVING clause can show you instances where duplicates exist:

SELECT *
FROM locations
GROUP BY contract_id, order_position
HAVING COUNT(*) > 1

Upvotes: 1

Chris Farmer
Chris Farmer

Reputation: 25386

SELECT contract_id, order_position, count(*)
FROM locations
GROUP BY contract_id, order_position
HAVING count(*) > 1

Upvotes: 1

Joel Coehoorn
Joel Coehoorn

Reputation: 415665

select contract_id, order_position
from locations
group by contract_id, order_position
having count(*) > 1

Upvotes: 3

Related Questions