SQL MIN Function in a query - 3 columns Access

Question

I trying to use min function but it is not working when I try to see 3 columns. I have this table

TABLE

And using MIN() function over Field_2, I want to get this output

EXPECTED OUTPUT

My query is

SELECT FIELD_1, MIN(FIELD_2) FROM TABLE GROUP BY FIELD_1

If I add the column ID, I get all the same table.

duplicate of https://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group — Tab Alleman, May 17 '18 at 15:39

score 1 · Accepted Answer · answered May 17 '18 at 15:37

Use subquery instead

select * 
from table t
where field_2 = (select min(field_2) from table where field_1 = t.field_1);

However, you can also use LIMIT clause

select * 
from table t
where id = (select id
           from table 
           where field_1 = t.field_1
           order by field_2 asc
           LIMIT 1);

However, some DBMS ha no LIMIT clause such (SQL Srver) so, use TOP instead like that :

. . . 
where id = (select top (1) id
            from table 
            where field_1 = t.field_1
            order by field_2 asc);

score 1 · Answer 2 · answered May 17 '18 at 15:37

You don't really want aggregation. You want to filter the rows. For this purpose, I often use a correlated subquery:

select t.*
from t
where t.field2 = (select min(t2.field2) from t t2 where t2.field_1 = t.field_1);

Aggregation is most appropriate when the following two conditions are true:

The group by clause specifies the rows that you want. Each combination of group by keys results in a single row.
All the other columns combine values from multiple rows into a single value.

In your case, the second condition isn't true. You want all the columns from a specific row.

SQL MIN Function in a query - 3 columns Access

2 Answers2