Max Value is how much larger than 2nd largest number

[wcrimi]

I know how to find the maximum value in a table, but I don't know how to figure out how much larger it is than the 2nd biggest number.



1
2
5
7
9
3
4

It's easy enough to find the "9". What's more difficult is to know that it's 2 point higher than the 2nd biggest number in the table. I need to do a series of calculations like this on grouped numbers and in each case know how much larger the max number is than the 2nd highest number in the group.

Any good ideas?

Thanks

Wayne

[isladogs]

Several methods e.g.
Create a SELECT TOP 2 query or a rank order query.
Whichever you choose, then make a new query with 2 copies of the original query.
Join using a suitable code and calculate the difference between them
Have a look at a similar example on my website http://www.mendipdatasystems.co.uk/g...lue/4594484854

OR possibly use a subquery http://allenbrowne.com/subquery-01.html

[wcrimi]

Several methods e.g.
Create a SELECT TOP 2 query or a rank order query.
Whichever you choose, then make a new query with 2 copies of the original query.
Join using a suitable code and calculate the difference between them
Have a look at a similar example on my website http://www.mendipdatasystems.co.uk/g...lue/4594484854

OR possibly use a subquery http://allenbrowne.com/subquery-01.html

Thanks. I'll give that a try.

Wayne

[isladogs]

OK - good luck. It shouldn't be too hard

[wcrimi]

OK - good luck. It shouldn't be too hard

It worked!

Thanks. I'll be able to use that technique for other applications also.

[isladogs]

Excellent. Which method did you use?

[wcrimi]

The first one. :-)

[isladogs]

As you say, the method is easily transferrable to other situations but the other two methods are more versatile still as they aren't limited to two records.