3rd highest from the max

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

Guest

I'm looking for a query that would return the 3rd highest, or 20th highest
value from list of values. Something like Max(field, -3), but obviously
that wouldn't work.

Any suggestions are appreciated!
 
Write a function (in vba) to OpenRecordset.
Move to the relevant record.
Return the value from the function.

There are no in-built functions to do this for you.
 
Something like this (try this out in the sample Northwind database)?

SELECT TOP 1 Products.ProductID, Products.ProductName,
Min(Products.UnitPrice) AS [Unit Price]
FROM Products
GROUP BY Products.ProductID, Products.ProductName
HAVING (Products.ProductID)
In (SELECT
TOP 20 Products.ProductID
FROM Products ORDER BY Products.UnitPrice DESC)
ORDER BY Min(Products.UnitPrice);


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________
 
Hi Tim,

I have put together a sample that includes a form where you can enter the
number of products that you which to be included in the evaluation. You can
download a copy here:

http://home.comcast.net/~tutorme2/samples/findproduct.zip

It is based on the Northwind Products table.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________


Tom Wickerath said:
Something like this (try this out in the sample Northwind database)?

SELECT TOP 1 Products.ProductID, Products.ProductName,
Min(Products.UnitPrice) AS [Unit Price]
FROM Products
GROUP BY Products.ProductID, Products.ProductName
HAVING (Products.ProductID)
In (SELECT
TOP 20 Products.ProductID
FROM Products ORDER BY Products.UnitPrice DESC)
ORDER BY Min(Products.UnitPrice);


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

Tim said:
I'm looking for a query that would return the 3rd highest, or 20th highest
value from list of values. Something like Max(field, -3), but obviously
that wouldn't work.

Any suggestions are appreciated!
 
Back
Top