CODEWITHSUNDEEP
mysql
bizkit1
bizkit1

Reputation: 23

MySQL select distinct doesn't work

I have a database with 1 table with the following rows:

id      name     date
-----------------------
1       Mike     2012-04-21
2       Mike     2012-04-25
3       Jack     2012-03-21
4       Jack     2012-02-12

I want to extract only distinct values, so that I will only get Mike and Jack once. I have this code for a search script: 

SELECT DISTINCT name FROM table WHERE name LIKE '%$string%' ORDER BY id DESC

But it doesn't work. It outputs Mike, Mike, Jack, Jack.

Why?

Upvotes: 1

Views: 7932

Answers (1)

Jonathan Leffler
Jonathan Leffler

Reputation: 753525

Because of the ORDER BY id DESC clause, the query is treated rather as if it was written:

SELECT DISTINCT name, id
  FROM table
 ORDER BY id DESC;

except that the id columns are not returned to the user (you). The result set has to include the id to be able to order by it. Obviously, this result set has four rows, so that's what is returned. (Moral: don't order by hidden columns — unless you know what it is going to do to your query.)

Try:

SELECT DISTINCT name
  FROM table
 ORDER BY name;

(with or without DESC according to whim). That will return just the two rows.

If you need to know an id for each name, consider:

SELECT name, MIN(id)
  FROM table
 GROUP BY name
 ORDER BY MIN(id) DESC;

You could use MAX to equally good effect.

All of this applies to all SQL databases, including MySQL. MySQL has some rules which allow you to omit GROUP BY clauses with somewhat non-deterministic results. I recommend against exploiting the feature.

For a long time (maybe even now) the SQL standard did not allow you to order by columns that were not in the select-list, precisely to avoid confusions such as this. When the result set does not include the ordering data, the ordering of the result set is called 'essential ordering'; if the ordering columns all appear in the result set, it is 'inessential ordering' because you have enough data to order the data yourself.

Upvotes: 6

Related Questions