Below is some SQL to pull a list of clients. As you can see I added the lines annotated by "-- ADDED"--a new field, a join and an order by. I m trying to display one client per row in my result set, but for each client, I want to display the most recent order. What s the best way to do this? Should I put a where in my JOIN or use the TOP 1? I suppose there are a few ways, but I want to make it as efficient as possible.
SELECT
CONVERT(NVARCHAR(255),client.identifyingnumber) As [Client ID],
CONVERT(VARCHAR,client.name) As [Client Name],
client.clientid As [Id],
CONVERT(CHAR(10),[ecom_order].[order_date],101) -- ADDED
FROM
client
JOIN clientstatus ON client.clientstatusid = clientstatus.clientstatusid
JOIN clienttype ON clienttype.clienttypeid = client.clienttypeid
-- keep left outer join since some clients won t have any orders, thus no last order
LEFT OUTER JOIN [ecom_order] ON client.clientid = [ecom_order].clientid -- ADDED
WHERE
client.name LIKE %_%
AND
client.name <= (
SELECT MAX(maxsubquery.ordercolumn) FROM (
SELECT
TOP 10 client.name AS ordercolumn
FROM
client
JOIN clientstatus ON client.clientstatusid = clientstatus.clientstatusid
JOIN clienttype ON clienttype.clienttypeid = client.clienttypeid
WHERE
client.name LIKE %_%
ORDER BY client.name
)
AS maxsubquery)
ORDER BY
client.name,
[ecom_order].order_date DESC -- ADDED
www.un.org/Depts/DGACM/index_spanish.htm 测试数据结果