jonas778 jonas778 - 6 months ago 13
SQL Question

Select two rows that are closest to a given value

I have the below table in MS Access.



Day ABC
365 25
548 35
730 37
913 58
1095 146





I want to query it such that I get the row before and after a given value of the Day column. This value is variable and can be e.g. value = 432
For this example the query would result the following table.



Day ABC
365 25
548 35





Because the given value = 432 is larger than the Day value of 365 and smaller than the Day value of 548.

What I managed to do is get one field, but not both. The following query gave me correct rows of the Day field.

Select Max(Day) As Day From Table Where Day < 432
UNION
Select Min(Day) As Day From Table Where Day > 432


When I use this code and add another field like ABC I get an error.


You tried to execute a query that does not include the specified
expression 'ABC' as part of an aggregate function.


Could you help me? I think this should be a really easy task. Thank you!

Answer

There's a couple of problems with your SQL. The error message occurs because you didn't add a GROUP BY ABC clause to both the SELECT statements.
You'll also get a circular reference as due to MAX(DAY) as DAY - you need to change the as Day to something else maybe as lDay.
You'll probably (I did) get a error Syntax Error calling it Table - pretty sure that's a reserved word.

Maybe this query will work better:

Select Top 1 Day, ABC From Table3 Where Day <= 913 ORDER BY Day Desc
UNION ALL
Select Top 1 Day, ABC From Table3 Where Day >= 913 ORDER BY Day Asc