CODEWITHSUNDEEP
mysqlsql-order-bydistinctexpressionengine
T Voskuilen
T Voskuilen

Reputation: 13

MYSQL DISTINCT ORDER BY doesn't giving correct order

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/

Upvotes: 1

Views: 2072

Answers (2)

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115530

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()

Upvotes: 1

John Woo
John Woo

Reputation: 263703

i don't know the SCHEMA of your table but you can try this query.

SELECT iTable.entry_id
FROM
    (SELECT DISTINCT e.entry_id, m.col_id_2
    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) as iTable
ORDER BY iTable.col_id_2 ASC

Upvotes: 0

Related Questions