Set a specific order in the query

Asked

Viewed 27 times

0

I have a query that selects all group members in rank order (owner, administrator and member) and by the date when the member joined the group. By the column access_level not being numerical I don’t know how I put in rank order.

SELECT * FROM group_memberships WHERE group_id = ? ORDER BY access_level ASC, STR_TO_DATE(date_joined, '%M %Y') ASC LIMIT;

access_level uses ENUM data type Owner, Administrator and Member, and it’s exactly in this order that I want.

How can I do this?

sql

  • Use the function FIELD. Ex: SELECT * FROM table ORDER BY FIELD(access_level, 'owner', 'adm', 'member')

2 answers

0


Você pode tentar ordenar com um CASE WHEN

ORDER
   BY CASE WHEN access_level = 'owner' THEN 1 
           WHEN access_level = 'administrator' THEN 2
           WHEN access_level = 'member' THEN 3
       END ASC,
STR_TO_DATE(date_joined, '%M %Y') ASC LIMIT;

0

You can bring a Fictitious sorting field in your query and sort by it.

SELECT IF(access_level="owner",1,IF(access_level = "administrator",2,3))AS Ornedacao
FROM group_memberships ORDER BY Ordenacao

Browser other questions tagged sql

You are not signed in. Login or sign up in order to post.