Swap the seats of neighbours

Easy

Question

The seats table lists the students of a class. Each seat has an id (1, 2, 3, and so on, with no gaps) and the student who sits there.

The teacher swaps every two neighbours: the students on seats 1 and 2 swap, the students on seats 3 and 4 swap, and so on. If the number of students is odd, the student on the last seat stays where they are.

Return the new seating plan as id and student. Sort by id from low to high.

The order of the rows matters for this question.

case
modulo
swap
lead
lag
classic

Read first: Lesson 46, SQL queries inside queries: subqueries, EXISTS, CTEs and recursion

Tables

1 table

These are the example rows. Run uses only these rows. Submit also tests your query on a second set of rows that you cannot see.

seats2 columns · 5 rowsnot run yet

CREATE TABLE seats (id INTEGER PRIMARY KEY, student TEXT NOT NULL);

Example rows of the seats table
id	student
1	Asha
2	Bhavna
3	Chirag
4	Divya
5	Eshan

About this SQL

The % operator gives the remainder in SQLite, Postgres and MySQL (7 % 2 is 1), so an odd id has id % 2 = 1. Do not rely on a MOD() function here. ORDER BY 1 sorts by the first column of the result. LEAD and LAG (window functions) let a row look at the next or the previous row.

SQLite

Opening the code editor…

Result

Run shows what your query gives on the example rows.Submit checks it on the example rows and on a hidden set of rows.

SQL · ready