Mysql Question posted in
The official MySQL documentation can be found here.

How to move the last row to the top in a table in MySQL?

I have a row in the following table which is the first row but added to the table at the end and now I’d like to modify the table in a way to show employee ids in order starting by number 1.
Here’s a Screenshot of the Table:

Issue:

(
  SELECT * FROM employees
  ORDER BY employee_id DESC
  LIMIT 1
)
UNION ALL
(
  SELECT * FROM employees
  WHERE employee_id != (SELECT MAX(employee_id) FROM employees)
);

Above code is the solution from chatGPT but still doesn’t move the last row to the top. Any clues?

Tags:

3

Answers


  1. 0

    SQL tables are inherently unordered sets. You can never assume nor trust the order of rows returned from a query that doesn’t explicitly order them.

    If you want the employee with employee_id of 1 to come first, you could order by it:

    SELECT   *
FROM     employees
ORDER BY employee_id
    Login or Signup to reply.
  2. 0 
    Try this query statement:
    (
      SELECT * 
      FROM employees
      ORDER BY employee_id DESC
      LIMIT 1
    )
    UNION ALL
    (
      SELECT * 
      FROM employees
      WHERE employee_id != (SELECT MAX(employee_id) FROM employees)
      ORDER BY employee_id
    );
    Login or Signup to reply.
  3. 0

    If you want to receive some definite rows ordering then you must apply ORDER BY expression which provides needed rows ordering.

    In your case – you want the row with the highest employee_id value to be placed on the top of the output.

    You may use, for example, the next method – obtain greatest value in the subquery and compare each row value with the value returned by the subquery, if equal then place the row first. The comparing result is either TRUE (which is 1) or FALSE (which is 0), so you’d order by this comparing result in DESCending order which puts the row(s) with the expression value of FALSE first.

    SELECT * 
FROM employees
CROSS JOIN ( SELECT MAX(employee_id) AS employee_id
             FROM employees ) AS max_employee_id
ORDER BY employees.employee_id = max_employee_id.employee_id DESC
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search