1

I have a SQL database in Android which has a table with values:

ID VAL 
1 5 
2 12 
3 8 
4 11 
5 1 
6 22 
7 20 
8 40
9 5

I want to write an SQL query where I put in the ID 7 and get out 12, 20 and 22. I want the neighbors determined based on VAL. It's possible that numbers in val appear multiple times. How do I do this?

Christian
  • 21,975
  • 33
  • 117
  • 195
  • `Select ID,VAL from table_name where ID between ( ID-1,ID+1 );` – Lucifer Sep 18 '14 at 08:52
  • @Kedarnath I'm pretty sure that you give you in this example 22, 20 and 40 (Id 6, Id 7 and Id 8). Do you really think it will return something different? – Christian Sep 18 '14 at 10:27

1 Answers1

1

You can try the following:

WITH yourvalue AS
(
  SELECT val FROM t WHERE id = 7
),
downvalue AS
(
  SELECT val 
  FROM t 
  WHERE val < (SELECT val FROM yourvalue)
  ORDER BY val DESC
  OFFSET 0 ROWS
  FETCH FIRST 1 ROWS ONLY
),
upvalue AS
(
  SELECT val 
  FROM t 
  WHERE val > (SELECT val FROM yourvalue)
  ORDER BY val
  OFFSET 0 ROWS
  FETCH FIRST 1 ROWS ONLY
)
select * from yourvalue
UNION
select * from downvalue
UNION
select * from upvalue


Output is: 12, 20, 22

I was able to test this only with MS SQL Server 2012. I believe that this is ANSI SQL, but it seems that SQLite does not like the syntax OFFSET/FETCH. So you can try to change from OFFSET 0 ROWS FETCH FIRST 1 ROWS ONLY to LIMIT 1 OFFSET 0.

Zanon
  • 22,850
  • 18
  • 101
  • 110