[AccessD] Obtaining 2 lowest values in a row

Hi Eric

> I have a table of records where I need to grab the 2 lowest values in a row.

> For example:

> 1  100  50  25  98  75  66
> 2   98  100 45  63  51  21

> so the result for row 1 would be 25 and 50 and the result for row 2 would be 21 and 45

> I want to create a query that produces this result

> 1   75 (sum of 25 and 50)
> 2   66 (sum of 21 and 45)

I think the easiest method would be to read the records into an array
with GetRows() and sort on this, but it can be done in SQL:

First, assemble the six fields into one using two union queries:

SELECT ID, V1 AS V, 1 AS N
FROM tblMin6
WHERE ID=1
UNION ALL
SELECT ID, V2 AS V, 2 AS N
FROM tblMin6
WHERE ID=1
UNION ALL
SELECT ID, V3 AS V, 3 AS N
FROM tblMin6
WHERE ID=1
UNION ALL
SELECT ID, V4 AS V, 4 AS N
FROM tblMin6
WHERE ID=1
UNION ALL
SELECT ID, V5 AS V, 5 AS N
FROM tblMin6
WHERE ID=1
UNION ALL
SELECT ID, V6 AS V, 6 AS N
FROM tblMin6
WHERE ID=1;

The other is identical but, of course, reads:
WHERE ID=2

Save these as qdyMin6All1 and qdyMin6All2.
The purpose of N is to distinguish two or more identical
next-to-minimum values in the next queries where TOP 2 is used.

Second, pick the two minimum values from these:

SELECT TOP 2
  *
FROM
  qdyMin6All1
ORDER BY
  V,
  N;

SELECT TOP 2
  *
FROM
  qdyMin6All2
ORDER BY
  V,
  N;

Save these as qdyMin6Min21 and qdyMin6Min22.

Now, create a union query to combine and sum the values:

SELECT
  ID,
  Sum(V) AS SumMinV
FROM
  qdyMin6Min21
GROUP BY
  ID
UNION ALL 
SELECT
  ID,
  Sum(V) AS SumMinV
FROM
  qdyMin6Min22
GROUP BY
  ID;

Save this as qdyMin6.
This will return two rows with the ID and the sums of your mins.

Have fun!

/gustav