Mysql order by two columns having ENUM type

Question

I have a doctors table where i have many columns, like id, name, email, etc. and two of them is is_featured and is_top.

These both column type is ENUM having value either 'y' or 'n'.

I want to get the doctors first having is_features='y' and then those doctors having is_top='y' and last is_featured='n' and is_top='n'

so basically i want the order of the doctors like these:

is_featured='y'
is_top='y'
is_featured='n'
is_top='n'

Can any one help how can i achieve this?

I am totally unaware about how to syntax these type of sql query?

It is better to use TINYINT(1) with values 1 and 0 for TRUE and FALSE, respectively, so the value gets casted to boolean correctly. — Josef Kufner
– Josef Kufner, Commented Feb 21, 2018 at 10:17

John Woo · Accepted Answer · 2018-02-21 06:37:20Z

1

There is a shortcut form for that in mysql:

SELECT ...
FROM ...
ORDER BY    is_featured = 'y' DESC,
            is_top = 'y' DESC,
            is_featured = 'n' DESC,
            is_top = 'n' DESC

Otherwise, if you want to make it standard which will run in other RDBMS as well, use CASE

SELECT ...
FROM ...
ORDER BY    CASE WHEN is_featured = 'y' THEN 1 ELSE 0 END DESC,
            CASE WHEN is_top='y' THEN 1 ELSE 0 END DESC,
            CASE WHEN is_featured='n' THEN 1 ELSE 0 END DESC,
            CASE WHEN is_top='n' THEN 1 ELSE 0 END DESC

answered Feb 21, 2018 at 6:37

John Woo

265k70 gold badges509 silver badges500 bronze badges

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

Collectives™ on Stack Overflow

Mysql order by two columns having ENUM type

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related