I have this sql query that I am trying to understand. Can I get a line by line explanation please. I know the first line Initialise variable. Second line gets the minimum value.
x
set @r1=0, @r2=0, @r3=0, @r4=0;
select min(Doctor), min(Professor), min(Singer), min(Actor)
from(
select case when Occupation='Doctor' then (@r1:=@r1+1)
when Occupation='Professor' then (@r2:=@r2+1)
when Occupation='Singer' then (@r3:=@r3+1)
when Occupation='Actor' then (@r4:=@r4+1) end as RowNumber,
case when Occupation='Doctor' then Name end as Doctor,
case when Occupation='Professor' then Name end as Professor,
case when Occupation='Singer' then Name end as Singer,
case when Occupation='Actor' then Name end as Actor
from OCCUPATIONS
order by Name
) Temp
group by RowNumber
Advertisement
Answer
The variables are enumerating the rows for each occupation separately. You can see this by running the subquery to see the results.
It is doing the equivalent of:
select max(case when occupation = 'Doctor' then name end) as doctor,
max(case when occupation = 'Professor' then name end) as professor,
max(case when occupation = 'Singer' then name end) as singer,
max(case when occupation = 'Actor' then name end) as actor
from (select o.*,
row_number() over (partition by occupation order by name) as seqnum
from occupations o
) o
group by seqnum;
This is a better way to write the query in MySQL 8+, because such use of variables has been deprecated. What it is doing is putting the occupations in separate columns:
occupation name
doctor A
doctor B
singer C
Turns into:
doctor professor singer actor
A C
B
The intermediate step is:
occupation name seqnum
doctor A 1
doctor B 2
singer C 1