why the last function in msaccess allow me to bypass the group by?

I have the following query on msaccess

```
SELECT Trim([T13_RefSupplier_France_List].[Supplier_code]) AS Supplier_code,
Trim([T13_RefSupplier_France_List].[Art]) AS Internal_reference,
Trim([fp_rcli]) AS Supplier_reference,
Last(Trim([ar_fami])) AS Family_code,
Last(Trim([fp_upri])) AS Purchasing_unit,
Last(Nz([fp_pcde],0)) AS Purchasing_price,
Last(Supplier_currency_France.Recode) AS Purchasing_currency,
Last("") AS Consigned,
Last(0) AS Eco_order_qty,
Last(CDbl(Nz(Nz([fp_cond],[ar_qcdi]),0))) AS Pack_order_qty,
Last(IIf([fp_minc]=0,Nz([fp_cond],[ar_qcdi]),[fp_minc])) AS Min_order_qty,
0 AS Min_order_value,
0 AS Product_grossweight,
0 AS Product_grosscube,
Last(gpfprodu_France.fp_dela) AS Leadtime_days,
Nz([Localisation_France].[Site],"Poitiers") AS Site,
CDbl(Nz([Active],-1)) AS Supplier_active,
Max(IIf([ar1_pdanz]=0,NULL,[ar1_pdanz])) AS Ref_price
FROM ((T13_RefSupplier_France_List
LEFT JOIN ((gparticl_France
LEFT JOIN Localisation_France ON gparticl_France.ar_loco=Localisation_France.Localisation)
LEFT JOIN [gpartic1_France] ON gparticl_France.ar_code=[gpartic1_France].ar1_code) ON T13_RefSupplier_France_List.Art=gparticl_France.ar_code)
LEFT JOIN ((gpfprodu_France
LEFT JOIN gpfourni_France ON gpfprodu_France.fp_four=gpfourni_France.fo_code)
LEFT JOIN Supplier_currency_France ON gpfourni_France.fo_monn=Supplier_currency_France.fo_monn) ON (T13_RefSupplier_France_List.Supplier_code=gpfprodu_France.fp_four)
AND (T13_RefSupplier_France_List.Art=gpfprodu_France.fp_arti))
LEFT JOIN T13_RefSupplier_France_SupplierActive ON (T13_RefSupplier_France_List.Art=T13_RefSupplier_France_SupplierActive.Art)
AND (T13_RefSupplier_France_List.Supplier_code=T13_RefSupplier_France_SupplierActive.Supplier_code)
GROUP BY Trim([T13_RefSupplier_France_List].[Supplier_code]),

What is the behaviour of the LAST function?

From the documentation:



These functions return the value of a specified field in the first or last record, respectively, of the result set returned by a query. If the query does not include an ORDER BY clause, the values returned by these functions will be arbitrary because records are usually returned in no particular order.


This does not say anything about the behaviour of LAST when a GROUP BY clause is present, but from testing it appears that FIRST and LAST return values from the row encountered first or last within each group.

Without an ORDER BY clause the row chosen (per group) by FIRST and LAST is essentially arbitrary. The important point is that the values chosen by multiple FIRST and LAST functions will come from the same row.

This last point means you cannot just replace FIRST or LAST by MIN or MAX (aside from the different semantics) because the minimum and maximum will generally not be from the same source row.


Let's say I want to transpose the LAST function to SQL Server: What should I do?

It is essentially impossible to duplicate this exactly, since the Access behaviour is not precisely defined; it's not possible to predict which row will be chosen as FIRST or LAST in all but the simplest cases.

That said, if you can improve the query semantic to have a deterministic choice for FIRST or LAST row within each group, a general translation would be to number each row (ascending or descending per group), then choose values from the row numbered 1.

The row numbering can be done with ROW_NUMBER. Within the OVER clause, the GROUP BY columns go in the PARTITION BY section, with deterministic ordering provided in the ORDER BY section. You will need to write a subquery or use a Common Table Expression (CTE) to filter row number to 1.

For example:

DECLARE @Table1 table
(
    ID integer IDENTITY NOT NULL,
    GroupID integer NOT NULL,
    [Data] integer NOT NULL
);

INSERT @Table1
    (GroupID, [Data])
VALUES
    (1, 3),
    (1, 2),
    (1, 1),
    (2, 6),
    (2, 5),
    (2, 4);

-- FIRST (ordered by ID ASC within GroupID)
WITH Numbered AS
(
    SELECT
        T.GroupID,
        T.[Data],
        rn = ROW_NUMBER() OVER (
                PARTITION BY T.GroupID
                ORDER BY T.ID ASC)
    FROM @Table1 AS T
)
SELECT
    N.GroupID,
    N.[Data]
FROM Numbered AS N
WHERE
    N.rn = 1;

-- LAST (ordered by ID DESC within GroupID)
WITH Numbered AS
(
    SELECT
        T.GroupID,
        T.[Data],
        rn = ROW_NUMBER() OVER (
                PARTITION BY T.GroupID
                ORDER BY T.ID DESC)
    FROM @Table1 AS T
)
SELECT
    N.GroupID,
    N.[Data]
FROM Numbered AS N
WHERE
    N.rn = 1;

Demo: db<>fiddle

In SQL Server 2012 or later, this can also be done with the FIRST_VALUE and LAST_VALUE windowed functions, but the execution plan may be less efficient. Also, these windowed functions are not aggregates, so you need to write the expression so that it returns the same value for every row per group, then apply an arbitrary aggregate. For example (using non-deterministic ordering just for variety):

DECLARE @Table1 table
(
    ID integer IDENTITY NOT NULL,
    GroupID integer NOT NULL,
    [Data] integer NOT NULL
);

INSERT @Table1
    (GroupID, [Data])
VALUES
    (1, 3),
    (1, 2),
    (1, 1),
    (2, 6),
    (2, 5),
    (2, 4);

WITH Windowed AS
(
    SELECT
        T.GroupID,
        fv = FIRST_VALUE(T.[Data]) OVER (
                PARTITION BY T.GroupID
                ORDER BY T.GroupID
                ROWS BETWEEN UNBOUNDED PRECEDING
                AND UNBOUNDED FOLLOWING)
    FROM @Table1 AS T
)
SELECT
    W.GroupID,
    [Data] = MIN(W.fv)   -- arbitrary aggregate
FROM Windowed AS W
GROUP BY
    W.GroupID;

WITH Windowed AS
(
    SELECT
        T.GroupID,
        lv = LAST_VALUE(T.[Data]) OVER (
                PARTITION BY T.GroupID
                ORDER BY T.GroupID
                ROWS BETWEEN UNBOUNDED PRECEDING
                AND UNBOUNDED FOLLOWING)
    FROM @Table1 AS T
)
SELECT
    W.GroupID,
    [Data] = MAX(W.lv)   -- arbitrary aggregate
FROM Windowed AS W
GROUP BY
    W.GroupID;

Demo: db<>fiddle

Your third choice is to write a SQLCLR user-defined aggregate (UDA). It is not currently possible to guarantee deterministic ordering with these, but the implementation might more closely match what Access does. You would need to be careful that all UDA results were computed by the same operator, to ensure the results of multiple UDA calls all come from the same source row.

StackExchange Database Administrators Q#188420, answer score: 5