CODEWITHSUNDEEP
mysqlselectdistinct
BWLR
BWLR

Reputation: 127

MySQL SELECT DISTINCT not behaving as expected

Ok this has really got me confused.

This query returns the expected results albeit with duplicate car_id numbers

SELECT car_id FROM `Updates` ORDER BY `updates`.`created` DESC

These 2 queries return the same set of results:

SELECT distinct `Updates`.`car_id` FROM `Updates` ORDER BY `updates`.`created` DESC

SELECT car_id FROM `Updates` GROUP BY car_id ORDER BY `updates`.`created` DESC

See below though as to how they differ: See below though as to how they differ:

Upvotes: 0

Views: 373

Answers (3)

AMayer
AMayer

Reputation: 355

Would Something Like This Work:

SELECT * FROM Updates GROUP BY car_id ORDER BY created DESC

Upvotes: -1

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115530

I don't think that this is strictly relevant with your problem but:

SELECT DISTINCT car_id 
FROM Updates 
ORDER BY created DESC

is not valid standard SQL syntax. There may be many rows with same car_id and different created values. Which one should be used for the ordering?

Perhaps you want to rewrite the query so it returns meaningful results:

SELECT car_id 
FROM Updates
GROUP BY car_id 
ORDER BY MAX(created) DESC              --- or MIN(created)
                                        -- whatever suits you

Upvotes: 1

jzila
jzila

Reputation: 735

You're ordering by updates.created. Presumably this means that each distinct carid will come up, just not exactly where you expect it to. Try ordering by carid to perform the comparison.

Upvotes: 1

Related Questions