sql case when how to sort for each group after grouping?-CodePudding

I have a user table below are the fields

CREATE TABLE cmf_user (

    user_id,       -- user id
    online,        -- online, 0 offline, 1 do not disturb, 2 chatting, 3 online
    isauthor_auth, -- Whether the host is authenticated or not. 0 = No 1 = Yes. No host authentication is a common user
    login_time     -- Last login time
)

I now need online certified host -> offline certified host -> online non-certified host -> offline non-certified host Sort this is sql

select
    *
from
    cmf_user
where
    user_type = 2
    and
    user_status = 1
    and
    sex = 1
order by
    case
        when isauthor_auth = 1 and online != 0 then 1
        when isauthor_auth = 1 and online  = 0 then 2
        when isauthor_auth = 0 and online != 0 then 3
        when isauthor_auth = 0 and online  = 0 then 4
    end

Now there is another requirement that case then is divided into four groups. I want to do the login_time and the last login time for these four groups. How should I write in descending order? It is for each group to perform the last login time in descending order, not all

CodePudding user response：

Rather than putting complex logic in the ORDER BY clause, consider making it a column expression, then sorting by that

Like so:

SELECT
    u.*,

    CASE
        WHEN u.isauthor_auth = 1 AND u.online != 0 THEN 1
        WHEN u.isauthor_auth = 1 AND u.online  = 0 THEN 2
        WHEN u.isauthor_auth = 0 AND u.online != 0 THEN 3
        WHEN u.isauthor_auth = 0 AND u.online  = 0 THEN 4
    END AS sort_partition

FROM
    cmf_user AS u
WHERE
    u.user_type = 2
    AND
    u.user_status = 1
    AND
    u.sex = 1
ORDER BY
    sort_partition,
    u.user_id

My query sorts by user_id after sorting by sort_partition to ensure consistent sorting when the same query is run again in future - as the sort-order of rows with insufficiently specific ORDER BY criteria can be returned in any order that otherwise satisfies the ORDER BY clause.

CodePudding user response：

Simply do:

ORDER BY isauthor_auth DESC, online DESC

I.e. first of all comes isauthor_auth = 1 rows, then the isauthor_auth = 0 rows.

Within each isauthor_auth group, online comes before chatting etc, instead of random order.

Note: AFAIK, the MySQL optimizer will have problems with case expressions in the ORDER BY.