Getting first row of each group with SQL in Microsoft Access

Question

I am trying to get the first row of each group in the individual_id column, but I keep getting errors.

In the first section of the query I am just trying to SELECT the individual_id, pics, and species from my Train table and GROUP BY the individual_id:

SELECT individual_id, pics, species
FROM Train
GROUP BY individual_id

This alone throws an error saying that pics doesn't have an aggregate function, but I don't want to use an aggregate function on the data I want it to be the same table just grouped.

In the second part of the query I get an error in the WITH OWNERSHIP ACCESS declaration which I don't even have.

WITH  added_row_number AS 
(
    SELECT
        *,
        ROW_NUMBER() OVER (PARTITION BY individual_id ORDER BY pics DESC) AS row_number
    FROM 
        Train
)
SELECT
    *
FROM 
    added_row_number
WHERE 
    row_number = 1;

Access does not support ROW_NUMBER() function nor the OVER keyword. Does have a PARTITION function but it is very different from T-SQL version. — June7, Apr 11 '22 at 22:32

score 0 · Answer 1 · answered Apr 11 '22 at 23:22

GROUP BY means aggregation: ie collapsing multiple rows into a single row for each unique value of the GROUP BY expression. It is individual_id, in your case (in other words, Access attempts to return one and only one row for each individual_id, but doesn't know what to do with the other columns: pics, species.

You said that you wanted the 'FIRST ROW' of each group. MsAccess has a FIRST aggregation function you can use for this purpose:

SELECT individual_id, FIRST(pics) as FIRST_pics, FIRST(species) as FIRST_species
FROM Train
GROUP BY individual_id

The FIRST function does not have a way specifying which row (of the same inidividual_id) is to be selected; it simply chooses the first retrieved as the FIRST (like the ORDER BY clause in the ROW_NUMBER() OVER function other rdbms products have).

Getting first row of each group with SQL in Microsoft Access

1 Answers1