CODEWITHSUNDEEP
mysqlsqlselectsql-order-bymultiple-columns
Niraj Chauhan
Niraj Chauhan

Reputation: 7880

MySQL sort by column and column value

I have a situation here, I am having a join of two tables to get records, where one table is storing key value pair in two different columns(wordpress user meta table).

So heres my query:

SELECT 
    um.user_id
FROM
    sl_job_applications as ja,
    sl_usermeta as um
WHERE
    um.user_id = ja.user_id
        AND ja.job_id = 3
        AND ja.STAGE = 'Application'
        AND ja.STATUS = 'In progress'
group by ja.user_id
order by case when (um.meta_key = 'CURRENT_TOTAL_EXPERIENCE') then -1 else 2 end,
         um.meta_value asc
LIMIT 0 , 50;

The order by is not working here, my data is

user_id             meta_key                       meta_value

3                   CURRENT_TOTAL_EXPERIENCE       6

4                   CURRENT_TOTAL_EXPERIENCE       2

5                   CURRENT_TOTAL_EXPERIENCE       1

6

I hope you understand my table data,

My above query returns 6,4,5,3

But I am expecting this output: 6,5,4,3

Upvotes: 1

Views: 76

Answers (2)

Saharsh Shah
Saharsh Shah

Reputation: 29051

Try this:

SELECT um.user_id
FROM sl_job_applications AS ja
INNER JOIN sl_usermeta AS um ON um.user_id = ja.user_id
WHERE ja.job_id = 3 AND ja.STAGE = 'Application' AND ja.STATUS = 'In progress'
GROUP BY ja.user_id
ORDER BY CASE WHEN (um.meta_key = 'CURRENT_TOTAL_EXPERIENCE') THEN -1 ELSE 2 END,
         CAST(um.meta_value AS SIGNED) ASC
LIMIT 0, 50

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269503

Simply change the order by to have an aggregation function such as max() and the value:

order by min(case when (um.meta_key = 'CURRENT_TOTAL_EXPERIENCE') then -1 else 2 end),
         min(case when (um.meta_key = 'CURRENT_TOTAL_EXPERIENCE') then um.meta_value end) asc
LIMIT 0 , 50;

The first checks that the meta_key with that value exists. The second extracts the value and does the sort.

Upvotes: 1

Related Questions