MySQL order by priority list

Question

I need to order a table but I don't know how columns will be selected by user.

Example:

select * 
from table 
order by field1, field2, field3

But the user can change fields order: field3, field1, field2

I was thinking in something like that:

select * 
from table
order by 
some_fuction (
field1, priority
field2, priority
field3, priority
)

Where I can set the priority in each field. Is it possible?

Thanks, Anderson

How is the user accessing your data? Via a script? If so, let the script handle it — Ja͢ck, Jun 03 '12 at 14:04

score 1 · Answer 1 · edited May 23 '17 at 12:25

1

Using if, case you can get a solution.
You can look at various solutions posted on SO:

and may be more ...

edited May 23 '17 at 12:25

Community

answered Jun 03 '12 at 14:48

Ravinder Reddy

I wouldn't like to use if or case and make something like this: select * from table order by case id when 1 then field1 when 2 then field2 end case id when 1 then field1 when 2 then field2 end . . . – user1433588 Jun 03 '12 at 16:33
@user1433588 - These are the only options at MySQL level. Otherwise you have to decide at server script level, before framing the sql query. – Ravinder Reddy Jun 03 '12 at 17:51

score 0 · Answer 2 · edited Sep 21 '22 at 11:02

0

if the user is selecting order means... use the parameter to order filed

select * from table_name
order by ${filed_name}

case makes SQL big

edited Sep 21 '22 at 11:02

Abdulla Nilam

answered Sep 16 '22 at 14:53

Bow Bow Boww

2 Answers2