如何在 Access 查询中选择前 1 - 并实际让它工作 [英] How to select top 1 in Access query - and actually get it to work

问题描述

First let me point out that this is a repeat of multiple stack-overflow questions that all have answers - yet none of them solve my problem

For instance - these two: Access join on first record and How to select top 10 in Access query?

---

My problem should be simple - but - the obvious answers are not working.

I have two tables. Client and Transactions.

I want to return a list of clients with the last sale date. Easy right.... Select the columns for the client and then for sale date do a subquery that limits the return to 1 item.

SELECT 
[Client].[LastName] as C1,
[Client].[FirstName] as C2,
(SELECT TOP 1 Transactions.SaleDate FROM Transactions WHERE Transactions.ClientID=Client.ClientID  ORDER BY Transactions.SaleDate Desc) as C3,
[Client].[ClientID] as C4
FROM [Client]

BUT access is telling me that the subquery has this problem: "At most one record can be returned by this subquery."

Ah... but stack overflow has an answer to the "At most one record can be returned by this subquery." error.

--->"Your subquery is returning more than one result." "Try fixing the data using select top 1"

At most one record can be returned by this subquery. (Error 3354)

well... Great.

Is there something else in the SQL that could be confusing it that I am totally missing?

解决方案

Add that as a JOIN and try it, Access sql parsing may be busted (been there), try this:

SELECT 
    c.[LastName] as C1,
    c.[FirstName] as C2,
    sd.maxsaledate as C3,
    c.[ClientID] as C4
FROM 
    [Client] c
    left join (
      select clientid, max(SaleDate) as maxsaledate from transactions group by clientid
    ) sd on
    c.ClientID = sd.ClientID

这篇关于如何在 Access 查询中选择前 1 - 并实际让它工作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！