How to use session variables with ORDER BY DESC?-CodePudding

I have a chat table:

id  msg         date
1   Hello!      2022-10-06
2   Hola!       2022-10-06
3   Bonjour!    2022-10-07
4   Ciao!       2022-10-08
5   Oi!         2022-10-08

What I am trying to achieve is that, I want to give each row a label called 'is_first_kind', with a value of 1 if the message is the first message from that particular date or 0 if not. This is my query:

select id, 
       msg ,
       `date` ,
       case when rn =1 then 1 else 0 end as is_first_kind
from ( SELECT  id, msg ,`date`,rn
       FROM (  SELECT *, IF(@prev <> `date`, @rn:=0,@rn), @prev:=`date`, @rn:=@rn 1 AS rn
               FROM chat, (SELECT @rn:=0) rn, (SELECT @prev:='') prev
               ORDER BY id asc, `date` asc 
            ) t 
     ) x

This is the result:

id  msg         date        is_first_kind
1   Hello!      2022-10-06  1
2   Hola!       2022-10-06  0
3   Bonjour!    2022-10-07  1
4   Ciao!       2022-10-08  1
5   Oi!         2022-10-08  0

As you can see, the first message from each date group has been assigned a value of 1. Great. This is exactly the data what I wanted. I But I want the results to be in descending order, without affecting the 'is_first_kind_column', like this:

id  msg         date        is_first_kind
5   Oi!         2022-10-08  0
4   Ciao!       2022-10-08  1
3   Bonjour!    2022-10-07  1
2   Hola!       2022-10-06  0
1   Hello!      2022-10-06  1

I tried this query:

select id, 
       msg ,
       `date` ,
       case when rn =1 then 1 else 0 end as is_first_kind
from ( SELECT  id, msg ,`date`,rn
       FROM (  SELECT *, IF(@prev <> `date`, @rn:=0,@rn), @prev:=`date`, @rn:=@rn 1 AS rn
               FROM chat, (SELECT @rn:=0) rn, (SELECT @prev:='') prev
               ORDER BY id desc, `date` desc 
            ) t 
     ) x

But this results in:

id  msg         date        is_first_kind
5   Oi!         2022-10-08  1
4   Ciao!       2022-10-08  0
3   Bonjour!    2022-10-07  1
2   Hola!       2022-10-06  1
1   Hello!      2022-10-06  0

As you can see, the 'is_first_kind' changes and now it assigns value of 1 to the last item in each particular date group instead of the first item. How to fix this? Any help would be appreciated.

CodePudding user response：

Just do

select * from ( subquery ) a order by date desc