CODEWITHSUNDEEP
sqlvbams-access
jonas778
jonas778

Reputation: 85

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!

Upvotes: 1

Views: 571

Answers (2)

Gustav
Gustav

Reputation: 55961

You could also be a little fancier: 

Select Top 2 Day, ABC From Table3 Order By Abs(Day - 432) Desc

Note please, that the result will match the title of the question, not necessarily your detailed explanation ...

Upvotes: 0

Darren Bartrup-Cook
Darren Bartrup-Cook

Reputation: 19837

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

Upvotes: 1

Related Questions