SQL Ordering Data from row "n+1" to "n-1"

Question

I have a simple question. In MySQL, consider a row "n", how can we order rows by id (for example), but start from the row "n+1" and end to the row "n-1" ?

Thanks !

EDIT : I ommit to precise that I seek the query in MySQL.

From an answer below, here an example :

ID
---
1
2
3
4   <--N
5
6

I want Desired Results ordered as follows

5   <--N + 1
6
1
2
3   <--N - 1

Sample data and desired results? Also, which RDBMS as there may be quicker solutions that are vendor specific. — sgeddes, Jun 14 '13 at 18:20
You mean you only need 3 rows in the result (n + 1, n, n - 1)? — Branko Dimitrijevic, Jun 14 '13 at 18:33

score 1 · Accepted Answer · answered Jun 14 '13 at 18:35

1

So you mean. For a table

ID
---
1
2
3
4   <--N
5
6

You want Desired Results ordered as follows?

5   <--N + 1
6
1
2
3   <--N - 1

If so

SELECT ID
FROM T
WHERE ID <> 4
ORDER BY CASE WHEN ID > 4 THEN 0 ELSE 1 END, ID

answered Jun 14 '13 at 18:35

Martin Smith

438,706
87
741
845

Thank you for your answer. You understood as well my requirement. But I ommit to say that I seek the query for MySQL. I'm editing my question. – Mehdi Chamouma Jun 16 '13 at 08:05
@MehdiChamouma - This answer is standard SQL and works in MySQL. [SQL Fiddle](http://www.sqlfiddle.com/#!2/1d9bc/1) – Martin Smith Jun 16 '13 at 08:27
My mistake. I need this for Doctrine2 (in DQL) and Case statement is not availaible. So I'll repost for a more specific request. Thank a lot ! – Mehdi Chamouma Jun 16 '13 at 09:20
@MehdiChamouma - Ah I've never used DQL so I have no idea if there is any simple equivalent to `CASE` there. – Martin Smith Jun 16 '13 at 09:24
wouldn't using the `if` statement be easier since it's only 1 case ? `ORDER BY if(ID > 4,0,1)` – Mohammad AbuShady Jun 16 '13 at 15:25
@MohammadAbuShady Yes. That is not standard SQL though. – Martin Smith Jun 16 '13 at 15:52

score 0 · Answer 2 · answered Jun 14 '13 at 18:25

0

Assuming table MyTable with integer column N:

SELECT *
 from MyTable
 where Id between N-1 and N+1
 order by N desc

answered Jun 14 '13 at 18:25

Philip Kelley

39,426
11
57
92

score -1 · Answer 3 · answered Jun 14 '13 at 18:21

-1

You're asking how to sort by descending ?

Just stick

ORDER BY col a , col b DESC;

at the end

answered Jun 14 '13 at 18:21

SELECTCOUNTSTAR

100
2
11

Doesn't answer the OP question. – Kris Gruttemeyer Jun 14 '13 at 18:25

SQL Ordering Data from row "n+1" to "n-1"

3 Answers3

Linked