To get the lowest value is by using SELECT MIN(Sale) AS Low From
tblSale. But how do I get the second lowest. Are there any function to
choose nr 2 or 3 from bottom?

Helge

Use a derived query. Untested Access sql:
Select min(Sale) as NthLowest
From tblSale
Where Sale > Any (Select Top n Sale From tblSale Order by Sale)

Replace Top n with whatever number you want. The subquery gets the n rows
with the lowest values. If you had 15 rows with the same lowest value, this
query return that same value for the 2nd-lowest row. If you want to look at
values, not rows, change the Where clause to:
Where Sale > Any (Select Top n Q2.Sale From (Select Distinct tblSale.Sale
From tblSale) As Q2 Order by Q2.Sale)

This version takes the distinct sales values in the innermost query. The
next subquery gets the nth lowest values. The outer query gets the smallest
value larger than that nth-lowest value.

"nankerp" <(E-Mail Removed)> wrote in message
news:c98893f0-4731-4706-b671-(E-Mail Removed)...
> To get the lowest value is by using SELECT MIN(Sale) AS Low From
> tblSale. But how do I get the second lowest. Are there any function to
> choose nr 2 or 3 from bottom?
>
> Helge

One way

SELECT Min(Sale) as SecondLow
FROM tblSale
WHERE Sale <>
(SELECT Min(Sale) as FirstLow from tblSale)

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

nankerp wrote:
> To get the lowest value is by using SELECT MIN(Sale) AS Low From
> tblSale. But how do I get the second lowest. Are there any function to
> choose nr 2 or 3 from bottom?
>
> Helge

On 30 Apr, 18:23, John Spencer MVP <spen...@chpdm.edu> wrote:
> One way
>
> SELECT Min(Sale) as SecondLow
> FROM tblSale
> WHERE Sale <>
> * *(SELECT Min(Sale) as FirstLow from tblSale)
>
> John Spencer
> Access MVP 2002-2005, 2007-2009
> The Hilltop Institute
> University of Maryland Baltimore County
>
>
>
> nankerp wrote:
> > To get the lowest value is by using SELECT MIN(Sale) AS Low From
> > tblSale. But how do I get the second lowest. Are there any function to
> > choose nr 2 or 3 from bottom?
>
> > Helge– Skjul sitert tekst –
>
> – Vis sitert tekst –

Thank you very much. This sql work very well.

Helge