We help IT Professionals succeed at work.

Find next smallest number in an access table

Scamquist
Scamquist used Ask the Experts™
on
I have a table of Shop Floor information.  The table is sorted by Shop Order Number and Operation number.

The operation numbers are not sequential. They may be, for example:
10
20
29
30
40
45
47
50

Is there a way to select an operation (say 47) and find the next lowest number, which would be 45?

Thank you in advance for your help.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Select Max(Number) from Table where Number < 47
SELECT Number, ISNULL((SELECT MAX(t2.Number) FROM Table t2 WHERE t2.Number<t1.Number),0) NextNumber FROM Table t1
Top Expert 2009

Commented:
Something like this maybe?

SELECT TOP 1 * FROM [TableName] WHERE [ShopOrderNumber] < 47 ORDER BY [ShopOrderNumber]

Substitute your actual table name and field name in the above, of course...
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
Or another method:

SELECT T1.Number, Max(T2.Number) as NextLowest
FROM yourTable as T1
LEFT JOIN yourTable as T2
ON T1.Number > T2.Number
GROUP BY T1.Number

Author

Commented:
Thank you for the assist.
happy to help and thanks for the grading, I often find that simple is best!