CODEWITHSUNDEEP
sql
casusbelli
casusbelli

Reputation: 463

Sorting each case-when statements

Here is my users DB :

id | sex    | trending | date_registered
---+--------+----------+----------------
1  | male   | 1        | 29-04-2020
2  | male   | 1        | 28-04-2020
3  | male   | 0        | 27-04-2020
4  | female | 1        | 26-04-2020
5  | female | 1        | 25-04-2020
6  | female | 0        | 24-04-2020
7  | female | 0        | 23-04-2020
8  | male   | 1        | 22-04-2020
9  | male   | 0        | 21-04-2020

I use the following querie in order to return :

(1) Trending females

(2) Rest of females

(3) Trending males

(4) Rest of males

select
    id,
    sex,
    trending,
    date_registered
from users
order by
    case
        when sex = 'female' and trending = 1 then 1
        when sex = 'female' and trending = 0 then 2
        when sex = 'male' and trending = 1 then 3
        when sex = 'male' and trending = 0 then 4
    end

here is the demo

What I want on top of that is sorting by the most recent users first for each case-when statements.

For example when sex = 'female' and trending = 1 then 1 returns a ASC sorting, so the oldest users will be shown first.

When I try a DESC sorting on the case-when statements like bellow, it doesn't change anything :

order by
(CASE when sex = 'female' and trending = 1 then 1 END) DESC,
(CASE when sex = 'female' and trending = 0 then 2 END) DESC,
(CASE when sex = 'male' and trending = 1 then 3 END) DESC,
(CASE when sex = 'male' and trending = 0 then 4 END) DESC

Upvotes: 1

Views: 28

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1270463

I think you can write this as:

order by sex, trending desc

Here is your db<>fiddle.

Upvotes: 0

Nick
Nick

Reputation: 147206

Based on your requirements, you need to sort by sex ascending (so that females are first), trending descending (so that trending users are first) and date_registered descending (so that most recent users are first):

select
    id,
    sex,
    trending,
    date_registered
from users
order by sex, trending desc, date_registered desc

Output for your fiddle:

id  sex     trending    date_registered
4   female  1           26-04-2020
5   female  1           25-04-2020
6   female  0           24-04-2020
7   female  0           23-04-2020
1   male    1           29-04-2020
2   male    1           28-04-2020
8   male    1           22-04-2020
3   male    0           27-04-2020
9   male    0           21-04-2020

Updated demo

Upvotes: 2

Ady Junior
Ady Junior

Reputation: 1080

Put DESC only after CASE block(After END keyword):

select
    id,
    sex,
    trending,
    date_registered
from users
order by
    case
        when sex = 'female' and trending = 1 then 1
        when sex = 'female' and trending = 0 then 2
        when sex = 'male' and trending = 1 then 3
        when sex = 'male' and trending = 0 then 4
    end DESC

Upvotes: 0

Related Questions