[AccessD] Selecting Nth Max or Min from a table

Hi all

In the Oracle Magazine of Mar/Apr a method for this is shown:

<SQL>

SELECT
  MIN(col1)
FROM
  (SELECT
    col1
  FROM
    (SELECT DISTINCT
      col1
    FROM
      Tab1
    ORDER BY
      code DESC)
  WHERE
    RowNum <= &N)

</SQL>

This doesn't work with Jet.

However, using TOP N, this can even be done simpler in Access (Jet)
should you ever need it. Replace N with that from Nth.

Here is for the Nth Min:

<SQL>

SELECT
  MAX(A.col1) AS NthMin
FROM
  [SELECT DISTINCT TOP N
    col1
  FROM
    tblTable
  ORDER BY
    col1 ASC]. AS A;

</SQL>

Here is for the Nth Max:

<SQL>

SELECT
  MIN(A.col1) AS NthMax
FROM
  [SELECT DISTINCT TOP N
    col1
  FROM
    tblTable
  ORDER BY
    col1 DESC]. AS A;

</SQL>

Those not familiar with the strange [..]. syntax may look up the
archives for "SQL in-line subquery" 

/gustav