CODEWITHSUNDEEP
mysqlsql
Jaquarh
Jaquarh

Reputation: 6693

How to query non duplicate rows but keep the primary key

Due to user error of refreshing the form request, I have inherited multiple duplicate rows in my database. The concept is a live tracker for a charity event where every hour, the distance is updated. Since they tried to refresh the admin page after inputting the new distance to get back onto it, it resubmitted it tonnes of times.

I tried to change my SQL query to only pull out DISTINCT rows. This works for the single column of total_distance however, I need the primary key id to be pulled out with it.

If I try:

SELECT DISTINCT total_distance, id
FROM ptsd_cycle
ORDER BY total_distance DESC

I still get the duplicate entries since the ID's cause them to be distinct. How can I query only non duplicate rows?

Upvotes: 1

Views: 124

Answers (1)

Rajen Raiyarela
Rajen Raiyarela

Reputation: 5634

Try doing grouping on distance as you say it is duplicated, and get minimum of the id row which will give you first record with that distance value. 

SELECT MIN(id) AS id, total_distance 
FROM ptsd_cycle 
GROUP BY total_distance 
ORDER BY total_distance DESC

Upvotes: 4

Related Questions