How do I get the second lowest by using SQL/Query

  • Thread starter Thread starter nankerp
  • Start date Start date
N

nankerp

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.
 
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
 
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





– Vis sitert tekst –
Click to expand...

Thank you very much. This sql work very well.

Helge
 
Back
Top