por que a última função no msaccess me permite ignorar o grupo?

Qual é o comportamento da LASTfunção?

Da documentação :

Essas funções retornam o valor de um campo especificado no primeiro ou último registro, respectivamente, do conjunto de resultados retornado por uma consulta. Se a consulta não incluir uma cláusula ORDER BY, os valores retornados por essas funções serão arbitrários, pois os registros geralmente não são retornados em uma ordem específica.

Isso não diz nada sobre o comportamento de LASTquando uma GROUP BYcláusula está presente, mas ao testar parece que FIRSTe LASTretorna valores da linha encontrada primeiro ou por último em cada grupo.

Sem uma ORDER BYcláusula, a linha escolhida (por grupo) por FIRSTe LASTé essencialmente arbitrária. O ponto importante é que os valores escolhidos pelas funções multiple FIRSTe virão da mesma linha .LAST

Este último ponto significa que você não pode simplesmente substituir FIRSTou LASTpor MINou MAX(além das diferentes semânticas), porque o mínimo e o máximo geralmente não serão da mesma linha de origem.

Digamos que eu queira transpor a LASTfunção para o SQL Server: O que devo fazer?

É essencialmente impossível duplicar isso exatamente, pois o comportamento do Access não é definido com precisão; não é possível prever qual linha será escolhida como FIRSTou LASTem todos, exceto nos casos mais simples.

Dito isso, se você puder melhorar a semântica da consulta para ter uma opção determinística para FIRSTou LASTlinha dentro de cada grupo, uma tradução geral seria numerar cada linha (crescente ou decrescente por grupo) e, em seguida, escolha valores da linha numerada 1.

A numeração das linhas pode ser feita com ROW_NUMBER. Dentro da OVERcláusula, as GROUP BYcolunas vão na PARTITION BYseção, com ordenação determinística fornecida na ORDER BYseção. Você precisará escrever uma subconsulta ou usar uma expressão de tabela comum (CTE) para filtrar o número da linha para 1.

Por exemplo:

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;

Demonstração:db<>fiddle

No SQL Server 2012 ou posterior, isso também pode ser feito com as funções FIRST_VALUEe em LAST_VALUEjanela, mas o plano de execução pode ser menos eficiente. Além disso, essas funções em janela não são agregadas, portanto, você precisa escrever a expressão para que ela retorne o mesmo valor para cada linha por grupo e, em seguida, aplicar uma agregação arbitrária. Por exemplo (usando ordenação não determinística apenas para variedade):

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;

Demonstração:db<>fiddle

Sua terceira opção é escrever um agregado definido pelo usuário (UDA) SQLCLR. Atualmente, não é possível garantir a ordenação determinística com eles, mas a implementação pode corresponder mais ao que o Access faz. Você precisaria ter cuidado para que todos os resultados UDA fossem calculados pelo mesmo operador, para garantir que os resultados de várias chamadas UDA venham da mesma linha de origem.

RESUMO

• Com GROUP BY, você só pode incluir as colunas na GROUP BYcláusula, constantes e valores agregados.
• O SQL Server tem uma função de janela semelhante LAST_VALUE(), mas só existe no SQL Server 2012 ou posterior.

EXPLICAÇÃO DETALHADA

LAST()é o que chamamos de função agregada . As funções agregadas calculam um valor para todas as linhas em uma consulta (se não houver GROUP BY, ou para todas as linhas com os mesmos valores para as colunas na GROUP BYcláusula. No SQL Server (e, suponho, no Access), se você usar um função agregada em sua SELECTlista (a lista de colunas de dados a serem retornadas da consulta) em uma consulta sem GROUP BYcláusula, então tudo que está sendo retornado deve ser:

• Valores que não dependem dos dados da sua tabela:
  • valores constantes como Consignedou Min_order_valueem sua consulta
  • os resultados de funções que não operam nos dados da tabela, como NOW()no Access ou GETDATE()no SQL Server
• Os resultados de funções agregadas (geralmente operando em colunas das tabelas na consulta), como as LAST()funções de sua primeira consulta ou Max(IIf([ar1_pdanz]=0,NULL,[ar1_pdanz]))em ambas as consultas.

Se você tiver uma GROUP BYcláusula, há uma adição ao acima: a SELECTlista também pode incluir as colunas (ou cálculos) listadas na GROUP BYcláusula. Geralmente, eles precisam aparecer exatamente da mesma forma nos dois lugares (por exemplo, se você GROUP BY SUBSTRING(field1,1,20), não pode ter apenas field1na SELECTlista).

Quando você coloca Last(Trim([ar_fami]))em sua SELECTlista, você está basicamente dizendo ao Access: "Haverá vários valores para ar_famino meu grupo, mas eu realmente não me importo com quais são esses valores; apenas me dê o valor da última linha em que você acontece ver."

Se você incluir Trim([ar_fami])na SELECTlista e no arquivo GROUP BY, estará informando ao Access que deseja ver cada valor exclusivo em sua saída e os subtotais/médias/qualquer coisa desses valores, juntamente com o restante das colunas no arquivo GROUP BY.

Quando você tenta incluir Trim([ar_fami])na SELECTlista, mas não no GROUP BY, o Access fica confuso: você pode ter vários valores para cada combinação possível das GROUP BYcolunas e não tem como dizer qual desses valores você gostaria de ver.

Nota para nós humanos: na prática, é possível que para todas as linhas em cada um de seus grupos, na realidade todos os valores sejam exatamente os mesmos. No entanto, os bancos de dados geralmente precisam decidir como obterão os dados antes de realmente começarem a obtê-los; e, nesse ponto, não há como saber se todos os ar_famivalores de cada grupo serão iguais. Então, tem que saber lidar com valores diferentes com antecedência.

By the way - você deve ser capaz de tirar as constantes da GROUP BYcláusula em sua segunda consulta (o ""e os múltiplos 0s). Como são sempre iguais, não podem alterar o agrupamento das linhas.

Quanto à sua segunda pergunta, a partir do SQL Server 2012, você tem acesso a uma função semelhante lá. O nome é um pouco diferente: LAST_VALUE()em vez de LAST(). Além disso, LAST_VALUE()é uma "função de janela"; ele requer uma OVER()cláusula, definindo os grupos com os quais trabalha e a ordem que usará, e os resultados são calculados para cada linha no conjunto de resultados, não para um grupo inteiro.

Você teria que criar uma consulta complicada (ou, mais provavelmente, um conjunto de consultas aninhadas) para capturar valores não agrupados do último registro visto em cada grupo.

Na prática, esses valores parecem ser de utilidade limitada. Se você está agregando o total de vendas dos vendedores em quatro locais diferentes, realmente importa que o nome do último vendedor tenha sido "Tony Warner"? Especialmente porque é possível que você execute a mesma consulta dois dias depois, com os mesmos dados subjacentes, gerando os mesmos resultados de grupo para totais e subtotais - mas observando que o nome do último vendedor era "Frank Northrup"? Pelo menos no SQL Server, o SQL classificará os dados em vários grupos para obter os valores agregados e aplicará o ORDER BYque você especificou; mas depende de você qual é essa ordem. Se oORDER BYnão inclui a coluna da qual você está obtendo o último valor ou algo que está vinculado a um valor específico, a "última" linha pode ser diferente cada vez que você chama a função, mesmo no mesmo conjunto de dados.

Ainda assim, obviamente, algumas pessoas acham que vale a pena usar. Eu posso suspeitar que essas pessoas realmente não entendem o que estão recebendo de volta, ou (pior) o que as pessoas olhando para seus resultados vão supor, mas talvez haja algum valor definido para comandar que eu simplesmente não estou vendo. Para mim, se os resultados são basicamente aleatórios, não vejo o valor.