Is it possible to do this task more efficiently? MySQL

user_16130923 0
Viewed 117

Currently, I have a task: for the presented table, to change students' order by replacing odd and even sequence (example below). There is only one condition, if the number of students is odd, that last number should not be changed. I currently wrote a code like this, however, for me, it seems pretty clumsy. How different and more efficient should this code be written?

CREATE TABLE student (id int, name varchar(128));
INSERT INTO student (id,name) VALUES
(1,'Aurimas'),
(2,'Darius'),
(3,'Eligijus'),
(4,'Giedrius'),
(5,'Justinas');

SELECT CASE 
    WHEN mod((SELECT id FROM student ORDER BY id DESC LIMIT 1),2) = 0 THEN 
        CASE  
            WHEN mod(id, 2) = 0 THEN id-1
            ELSE id=id+1
        END
    ELSE 
        CASE
            WHEN mod(id, 2) = 0 AND id <> (SELECT id FROM student ORDER BY id DESC LIMIT 1) THEN id-1
            WHEN mod(id, 2) = 1 AND id <> (SELECT id FROM student ORDER BY id DESC LIMIT 1) THEN id+1
            ELSE id
        END
    END AS new_id, name
FROM student
ORDER BY new_id ASC;

I have this:

id  name
1   Aurimas
2   Darius
3   Eligijus
4   Giedrius
5   Justinas

And it should look like this:

id  name
1  Darius
2  Aurimas
3  Giedrius
4  Eligijus
5  Justinas
mysql
3 Answers

You could group your ids by the value of (id+1) DIV 2. Then all you have to do is swap the minimum id with the maximum id in each pair of rows and make sure you handle the case when there are odd number of rows properly.

Something like this:

set @maxId = (select max(id) from student);
select
  if (
    @maxId = id and mod(id, 2) != 0,
    id, 
    if (
      mod((id+1), 2) = 0,
      id+1,
      id-1
    )
  ) as id,
  name
from student
order by id;

db<>fiddle

user_63672130

I'm not sure how you're defining 'efficient', but how about...

select ROW_NUMBER() OVER (
    ORDER BY ceiling(id/2), id desc
) new_id,name from student

The ROW_NUMBER function has been supported in MySQL since version 8.0.

EDIT: I suppose something like will satisfy the quibblers...

WITH cte (id,n) AS (SELECT id, ROW_NUMBER() OVER (ORDER BY id) n FROM student)   
SELECT x.name
     , ROW_NUMBER() OVER (ORDER BY CEILING(cte.n/2), x.id desc) new_id
  FROM student x
  JOIN cte 
    ON cte.id = x.id;
user_15296730

I would recommend to use the below query because

it has many advantages here I am calculating only one max id select but in your case for every case, one select will execute this will reduce that time and only one max id will be used for the odd case

your odd and even operation is the same but in odd you are skipping last record so I added that condition

SET @MAXID = (SELECT MAX(ID) FROM  STUDENT);
SELECT
    CASE
        WHEN MOD(@MAXID,2) != 0 AND ID=@MAXID THEN ID
        ELSE CASE WHEN MOD(ID, 2) = 0 THEN ID-1 ELSE ID+1 END
    END AS NEW_ID, NAME
FROM STACK_USER.STUDENT
ORDER BY NEW_ID ASC;

I think this would be useful in your case I have checked every condition in my local it will work properly

Screen Shot For Better Understanding:

Screen Shot For Better Understanding

user_157544850
Related