LeetCode 626: Exchange Seats [SQL]

Solution and walkthrough of leetcode database problem 626: Exchange Seats. I'm using MySQL but this solution should work in any SQL dialect such as PostgreSQL SQL Server, etc.
Link to the problem: leetcode.com/p...

[@amrqura]

Thanks alot for your explanation. I would say using window function can be more efficient here, here is an example:
select id,
case when id%2=1 then lead(student,1,student) over (order by id)
else lag(student) over(order by id)
End as student
from Seat

[@mritunzaysingh8978]

thank you sir .. for your answer it really meaningful and understandable

[@yeah6732]

Very good solution, thank you!!

[@minimumeffort1997]

I like how you solve all the problems with basic logic instead of using fancy terminologies. As programmers , we should stick to logic instead of using fancy built in functions.

[@frederikmuller]

yes, the goal is to make everyone understand and not use fancy terminology and functions just for the sake of using them.

[@anboyao6125]

You explained so well bro. A lot of questions I was so confused looking at the answer. You are so good!!!

[@mritunzaysingh8978]

I solved this query by using windows function
create TABLE seat
(
id int not null primary key,
name VARCHAR(20)
);
insert into seat VALUES(1,'Raman'),(2,'Sanjiv'),(3,'Saloni'),(4,'Shweta'),(5,'Mohit');
select * from seat;
select *,
case when id%2 != 0 then lead(name,1,name) over(ORDER by id)
when id%2 = 0 then lag(name) over(ORDER by id)
else name END as exchange_seat_student_name
from seat;

[@ahmadmohamadi]

MS SQL:
select id, case when mod(id,2)=0 then lag(student) over (order by id)
when lead(student) over (order by id) IS NULL then student
else lead(student) over (order by id)
end as student
from seat order by id

[@jackyychn7053]

Smart move, as I was struggling with exchanging names.

[@HenggaoCai]

You explain very well. Your explanations are clear.

[@kaushikpasikanti9025]

Clear, simple explanation!

[@aresnemeia]

Another logic approach is to get the previous and next student name and decide which to display, it is much cleaner and easier to understand solution::
SELECT id,
CASE
WHEN id % 2 = 0 THEN LAG(student, 1) OVER() #even show previous
WHEN id % 1 = 0 AND LEAD(student, 1) OVER() IS NOT NULL THEN LEAD(student, 1) OVER() #odd show next
ELSE student #last record/row
END as student
FROM Seat

[@vichitravirdwivedi]

what is the use of id%1=0

[@navneetberi910]

you explained it so well.

[@the_fat_journal]

Smart, thank you.

[@yourbestie4138]

good one

[@zhihaoxu8119]

Lengendary