mri mri - 20 days ago 5
SQL Question

Access: Minimum value across columns

I would like to do a (probably very simple) Access query where I get the minimum value from column A for each ID. But I'd also like to retain the corresponding values (values in the same row) for the other columns (in this case B & C).

My idea was to choose the 'Group by' function for the ID and the 'Minimum' function for the other columns. However, this will return the minimum value for each column (see example below). I tried to play around with some other functions but nothing really appears to work.

Here is an example to illustrate what I mean:
(This is a small part of my table)

ID A B C
1 5 10 10
1 6 8 7
1 4 5 9
2 8 2 4
2 5 7 3
2 3 4 6


This is what I want: (the minimum value in column A independent of column B & C)

ID A B C
1 4 5 9
2 3 4 6


But this is what I get: (the minimum value in each column for each ID)

ID A B C
1 4 5 7
2 3 2 3


Help is much appreciated!

Answer

Basically, you want to select all rows where A is the minimal A for the given ID. Translated to Access SQL, you get the following:

SELECT ID, A, B, C
  FROM myTable AS T1
 WHERE A = (SELECT MIN(A) 
              FROM myTable AS T2
             WHERE T2.ID = T1.ID)
Comments