We need to swap _exchange consecutive seats of students

In LINQ (Language Integrated Query) for C#, we can achieve the same result by iterating through the list and swapping consecutive elements.

using System;
using System.Collections.Generic;
using System.Linq;

public class Program
{
    public static void Main()
    {
        List students = new List
        {
            new Student { Id = 1, Name = "Amit" },
            new Student { Id = 2, Name = "Deepa" },
            new Student { Id = 3, Name = "Rohit" },
            new Student { Id = 4, Name = "Anjali" },
            new Student { Id = 5, Name = "Neha" },
            new Student { Id = 6, Name = "Sanjay" },
            new Student { Id = 7, Name = "Priya" }
        };

        var swappedStudents = students
            .Select((student, index) => new { student, index })
            .OrderBy(s => s.index)
            .Select((s, index) => new Student
            {
                Id = s.student.Id,
                Name = (index % 2 == 0 && index + 1 < students.Count) 
                       ? students[index + 1].Name 
                       : (index % 2 != 0) 
                         ? students[index - 1].Name 
                         : s.student.Name
            })
            .ToList();

        foreach (var student in swappedStudents)
        {
            Console.WriteLine($"Id: {student.Id}, Name: {student.Name}");
        }
    }
}

public class Student
{
    public int Id { get; set; }
    public string Name { get; set; }
}

• We use Select with an index to keep track of the position of each student.

• We then use a conditional operator to swap the names:

  • If the index is even and there is a next student, we take the next student's name.

  • If the index is odd, we take the previous student's name.

  • If it's the last student and the index is odd, the name remains unchanged.

 

Below is a simple flow diagram to visualize the process:

1. Start

2. Read the list of students

3. Iterate through the list:

   • If the current index is even:

     • Swap the current student's name with the next student's name.

   • If the current index is odd:

     • Swap the current student's name with the previous student's name.

4. If the list has an odd number of students:

   • The last student's name remains unchanged.

5. Output the updated list of students

As per my understanding, the goal is to swap the student names of consecutive rows. For example, Amit and Deepa should swap, Rohit and Anjali should swap, and so on. If there's an odd number of rows, the last student remains in place.

 In SQL, we can use a CASE statement along with the LEAD and LAG window functions to swap the names of consecutive rows.

SELECT
    id,
    CASE
        WHEN id % 2 = 1 THEN COALESCE(LEAD(student) OVER (ORDER BY id), student)
        WHEN id % 2 = 0 THEN LAG(student) OVER (ORDER BY id)
    END AS student
FROM students
ORDER BY id;

• LEAD(student) OVER (ORDER BY id) gets the next student's name.

• LAG(student) OVER (ORDER BY id) gets the previous student's name.

• CASE statement is used to swap the names:

  • If the id is odd, we take the next student's name.

  • If the id is even, we take the previous student's name.

• COALESCE checks that if there is no next student (i.e., for the last row), the current student's name remains unchanged.

Hi Shubham,

To swap consecutive seats of students, you can use SQL MOD function. Below is an example query to achieve this:

SELECT 
    CASE 
        WHEN MOD(id, 2) = 0 THEN id - 1 
        ELSE id + 1 
    END AS new_id,
    student
FROM 
    students_table
ORDER BY 
    new_id;

If your SQL engine does not support the MOD function (e.g., SQLite), you can use an equivalent expression like id % 2. Here’s the updated query that works without MOD:

SELECT 
    CASE 
        WHEN id % 2 = 0 THEN id - 1 
        ELSE id + 1                
    END AS new_id,
    student
FROM 
    students_table
ORDER BY 
    new_id;

Thank you!

You can use a CTE to swap theid values and make sure no duplicate keys are created during the update :

 

WITH SwappedSeats AS (
    SELECT 
        id AS original_id, 
        student, 
        CASE 
            WHEN id % 2 = 1 THEN id + 1  -- Odd id moves to the next seat
            WHEN id % 2 = 0 THEN id - 1  -- Even id moves to the previous seat
        END AS new_id
    FROM Students
)
UPDATE S
SET S.id = SS.new_id
FROM Students S
INNER JOIN SwappedSeats SS
ON S.id = SS.original_id