MYSQL DISTINCT ORDER BY doesn't giving correct order

Question

Update: Thanks everbody, things are working 100% now with this query:

SELECT e.entry_id
FROM exp_channel_titles AS e
INNER JOIN exp_matrix_data AS m ON m.entry_id = e.entry_id
WHERE e.channel_id = 2
GROUP BY e.entry_id
ORDER BY MIN(m.col_id_2) ASC

Here is my code.

Code from .home_laagste_rentes

{exp:channel:entries dynamic="no" fixed_order="0{embed:entry_ids}" disable="member_data|pagination|categories"}
<tr class="{switch="odd|even"}">
    <td><a href="#" title="{title}">{title}</a></td>
    <td>{dk:lowest col="rente" decimals="2" dec_point=","}%</td>
    <td>{count}</td>
</tr>
{/exp:channel:entries}

Code from page:

<table id="rowspan" cellspacing="0" class="tablesorter">
<thead>
    <tr>
        <th>Krediet aanbieder</th>
            <th>Rente</th>
            <th>Beoordeling</th>
    </tr>
</thead>
<tbody>
{embed="embeds/.home_laagste_rentes"
entry_ids="{exp:query sql="
    SELECT DISTINCT (e.entry_id)
    FROM exp_channel_titles AS e
    INNER JOIN exp_matrix_data AS m ON m.entry_id = e.entry_id
    WHERE e.channel_id = 2
    ORDER BY m.col_id_2 ASC
"}|{entry_id}{/exp:query}"}
</tbody>

col_id_2 is set as decimal(10,4)

entry_id is set as int

This is filtering duplicates but it's not giving the correct order. It looks like filtering duplicates is done random... See this link for output: http://postimage.org/image/9vzahuuez/

Answer 1

You have many, possibly different, m.col_id_2 values, for one e.entry_id.

Your query is not even valid ANSI SQL, because ORDER BY is evaluated after DISTINCT, so ORDER BY m.col_id_2 should create an error (if your MySQL setting was strict ANSI SQL).

Solution is to state which value should be used for the ordering, using GROUP BY instead of SELECT DISTINCT:

SELECT e.entry_id
FROM exp_channel_titles AS e
INNER JOIN exp_matrix_data AS m ON m.entry_id = e.entry_id
WHERE e.channel_id = 2
GROUP BY e.entry_id
ORDER BY MAX(m.col_id_2) ASC                  --- or MIN(), or AVG()