Wednesday, 27 May 2015

Mysql: Select last 2 elements ascending followed by 1st element

I want to select the last two elements in ascending order followed by the first element.

E.g. for 20 products now I'm getting
20, 19, 1 (ordered by id).
I'm trying to get 19, 20, 1.

Solution:
CREATE TABLE Table1
    (`id` int);
   
INSERT INTO Table1    (`id`) VALUES    (1),    (2),    (3),    (4),    (5),    (6),    (7),    (8),    (9),    (10),    (11),    (12),    (13),    (14),    (15),    (16),    (17),    (18),    (19),    (20);

SELECT id
  FROM
(
  (
    SELECT id, 0 sort_order
      FROM Table1
     ORDER BY id DESC
     LIMIT 2
  )
  UNION ALL
  (
    SELECT id, 1 sort_order
      FROM Table1
     ORDER BY id 
     LIMIT 1
  )
) q
 ORDER BY sort_order, id
Output:
| ID |
|----|
| 19 |
| 20 |
|  1 |
 

0 comments:

Post a Comment