MySQL sort by column and column value

Question

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

Gordon Linoff · Accepted Answer · 2013-12-24 15:02:30Z

1

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.

edited Dec 24, 2013 at 15:02

answered Dec 24, 2013 at 14:49

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Niraj Chauhan Over a year ago

Hey this is working, but just for knowledge if I want the blank value at last everytime, then what should I do?

Gordon Linoff Over a year ago

@ntechi . . . changing to min() should fix that problem.

Saharsh Shah · Accepted Answer · 2013-12-26 04:44:15Z

0

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

edited Dec 26, 2013 at 4:44

answered Dec 24, 2013 at 14:49

Saharsh Shah

29.1k9 gold badges53 silver badges85 bronze badges

Collectives™ on Stack Overflow

MySQL sort by column and column value

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related