Select query but show the result from record number 3

Question

I've seen this question: Select query but show the result from record number 3

Table

|id|   foo   |    bar   |
-------------------------
|1 |   aaa   |    123   |
|2 |   bbb   |    234   |
|3 |   ccc   |    345   |
|4 |   ddd   |    456   |

based on the link above I use the query

SELECT * 
FROM Table 
ORDER BY (id = 3) DESC

and the result is

|id|   foo   |    bar   |
-------------------------
|3 |   ccc   |    345   |
|1 |   aaa   |    123   |
|2 |   bbb   |    234   |
|4 |   ddd   |    456   |

So my question is, is there any way to show the result starts from record number 3,then 4, then 1 and the last is record number 2? So the result can be shown as follow:

|id|   foo   |    bar   |
-------------------------
|3 |   ccc   |    345   |
|4 |   ddd   |    456   |
|1 |   aaa   |    123   |
|2 |   bbb   |    234   |

My example above is only a model, my goal is how to sort the result from the record I choose to the max record then cycling to the first record. If I choose record number 3 then the result is

3,4,5,...,max,1,2

Is it possible?

Eric Petroelje · Accepted Answer · 2011-12-12T19:20:09.547

1

I think this would work:

ORDER BY (id = 3) OR (id = 4) DESC, id ASC

ETA: Based on your comment, it seems that what you really want is for 1 & 2 to display LAST. That would look like this:

ORDER BY id <= 2, id

edited Dec 12 '11 at 19:20

answered Dec 12 '11 at 18:55

Eric Petroelje

59,820
9
127
177

Yes, it works, but if you just only have 4 records just like the example. What if I have let say 20 records? – sapu lidi Dec 12 '11 at 19:02
@sapu - well then it would show as 3,4,1,2,5,6,7,etc. Is that not what you expected? If not, what did you want it to display if there were more than 4 records? – Eric Petroelje Dec 12 '11 at 19:08
My question is only a model. I expect, if the records more than 4 then the result shown as 3,4,5,...,max,1,2 – sapu lidi Dec 12 '11 at 19:15

score 1 · Answer 2 · answered Dec 12 '11 at 18:56

1

To force a specific order like that you need to use a CASE:

ORDER BY CASE WHEN id = 3 THEN 1
              WHEN id = 4 THEN 2
              WHEN id = 1 then 3
              WHEN id = 2 then 4
              ELSE 5 END ASC

answered Dec 12 '11 at 18:56

JNK

63,321
15
122
138

Alternative version: `ORDER BY id = 3 DESC , id = 4 DESC , id = 1 DESC , id = 2 DESC, id ASC` – piotrekkr Dec 12 '11 at 19:04

score 0 · Answer 3 · answered Dec 12 '11 at 20:20

This may not be perfectly ideal because it requires two queries, but it's completely specified and doesn't rely on using booleans for ordering:

(SELECT * 
 FROM Table 
 WHERE id >= 3
 ORDER BY id ASC)
UNION
(SELECT *
 FROM Table
 WHERE id < 3
 ORDER BY id ASC)

Select query but show the result from record number 3

3 Answers3