Creating rows for 5 higher and lower entries with closest matching values in same table in SQL-CodePudding

I'm very new to SQL and trying to structure a Java database query to pass in a row identifier code, return the values of all columns in that row, and the 5 closest higher and lower rows to a value in one of the original columns. I can find previous questions using a passed in fixed value, but don't know how to approach it when the value exists in the table.

This is my attempt so far:

SELECT * FROM (SELECT code, value FROM table1 t1 WHERE code = x) AS a

UNION ALL

SELECT * FROM (SELECT * from table1 t2 WHERE NOT code = x AND count <= t1.count order by count DESC LIMIT 5) AS b

UNION ALL

SELECT * FROM (SELECT * from table1 t3 WHERE NOT code = x AND count <= t1.count order by count ASC LIMIT 5) AS c

If anyone could point me in the right direction I would really appreciate it. Thanks

Example Table:

Code	Value
Australia	15
Mexico	22
Spain	36
Nigeria	87
Poland	55
Eritrea	17
Vietnam	26
Ireland	107
Sweden	55
Canada	26

Just as an example, but if I entered Australia as my code, I want to return that and the closest 4:

Code	Value
Australia	15
Eritrea	17
Mexico	22
Vietnam	26
Canada	26

CodePudding user response：

If there are no duplicates in the column Value:

SELECT *
FROM tablename
ORDER BY ABS(Value - (SELECT Value FROM tablename WHERE Code = 'Australia'))
LIMIT ?;

If there are duplicates:

SELECT *
FROM tablename
ORDER BY Code = 'Australia' DESC,
         ABS(Value - (SELECT Value FROM tablename WHERE Code = 'Australia'))
LIMIT ?;

Change ? to the total number of rows returned (including 'Australia').

See the demo.