CODEWITHSUNDEEP
mysqlselectsql-order-by
Victor.Palyvoda
Victor.Palyvoda

Reputation: 543

MySQL HowTo select unique values of column 2 ordered by column 1?

I have mytable with columns (column1 bigint AUTO_INCREMENT PRIMARY KEY, column2 bigint NOT NULL). And some test values:

+-+-+
|1|3|
|2|4|
|3|5|
|4|4|
+-+-+

I want to select unique values of column2 ordered by column1. So, I do this:

SELECT DISTINCT column2 FROM mytable ORDER BY -column1 LIMIT 0, 10

MySQL returns this:

+-+
|5|
|4|
|3|
+-+

But how to achieve this result?:

+-+
|4|
|5|
|3|
+-+

Upvotes: 0

Views: 106

Answers (2)

Rick
Rick

Reputation: 362

I don't think you can do that.

Consider this input (I'm changing the 2nd column to letters for clarity)

1   C
2   B
3   A
4   C
5   B

The distinct set of col 2 is A,B,C.

To sort by column one, which version of C is used? If we use

1   C

then the results are C,B,A.

But if you use

4   C

then the results are B,A,C.

There is not one unique order.

Now if you wanted to order by the first occurrence of each value in column 2, that is a different story:

select min(column1), column2
from mytable
group by column2
order by min(column1)

Upvotes: 1

Ezequiel Muns
Ezequiel Muns

Reputation: 7762

MySQL seems to first apply the DISTINCT function to the tuples and only after will it apply the ordering. You want to force it to first order and then apply DISTINCT by using a subquery:

SELECT DISTINCT column2 FROM (
    SELECT column2 
    FROM mytable 
    ORDER BY -column1
) AS a;

Upvotes: 1

Related Questions