How to secound highest value in the table?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,
Is there a function in Access that can find the second the highest value in
a table?

Max can get the highest value right?

Thanks
 
You can stack a couple of Max or DMax calls. For example, using DMax:

intMyVariable = DMax("MyField","MyTable", "MyField < " &
DMax("MyField","MyTable"))

If you had other criteria, you'd have to add them to both criteria statements.

The concept is the same if you're using Select Max(MyField) From... except
you'd create a subquery.

Barry
 
Very clever, Barry. It looked so weird I had to try it.
It was one of those "Why didn't I think of that?" moments.
It even works if you take it down another level to find 3rd place

dmax("[Jan]","Actual_res_export", "[Jan] < " &
DMax("[Jan]","actual_res_export", "[Jan] < " &
DMax("[Jan]","actual_res_export")))

Let's see now, there are 25,846 rows in this table. hmmmmmmmm :)
 
A concept I picked up when I had to write a really gnarly SQL Server stored
proc and I tried everything to avoid cursors. Domain aggregates can do the
same as correlated subqueries.

Barry

Klatuu said:
Very clever, Barry. It looked so weird I had to try it.
It was one of those "Why didn't I think of that?" moments.
It even works if you take it down another level to find 3rd place

dmax("[Jan]","Actual_res_export", "[Jan] < " &
DMax("[Jan]","actual_res_export", "[Jan] < " &
DMax("[Jan]","actual_res_export")))

Let's see now, there are 25,846 rows in this table. hmmmmmmmm :)

Barry Gilbert said:
You can stack a couple of Max or DMax calls. For example, using DMax:

intMyVariable = DMax("MyField","MyTable", "MyField < " &
DMax("MyField","MyTable"))

If you had other criteria, you'd have to add them to both criteria statements.

The concept is the same if you're using Select Max(MyField) From... except
you'd create a subquery.

Barry
Click to expand...
Click to expand...
 
Thanks Barry, it works.


Barry Gilbert said:
You can stack a couple of Max or DMax calls. For example, using DMax:

intMyVariable = DMax("MyField","MyTable", "MyField < " &
DMax("MyField","MyTable"))

If you had other criteria, you'd have to add them to both criteria statements.

The concept is the same if you're using Select Max(MyField) From... except
you'd create a subquery.

Barry
Click to expand...
 
Back
Top