CODEWITHSUNDEEP
sqlsql-servergreatest-n-per-group
Caballero
Caballero

Reputation: 12111

SQL Server: aggregate to single result

I have this query

SELECT      Client.ClientNo,
            Client.ContactName,
            Deal.Currency,
            MAX(Deal.DealDate)
FROM        Deal
JOIN        Client ON Deal.ClientNo = Client.ClientNo
GROUP BY    Client.ClientNo, Client.ContactName, Deal.Currency;

which gives me a result

1   John Smith  EUR 2014-10-07
1   John Smith  GBP 2014-11-12
2   Jane Doe    GBP 2014-09-17
2   Jane Doe    USD 2014-12-23
1   John Smith  USD 2013-11-13
2   Jane Doe    EUR 2012-09-06

Problem is, I need an aggregated result with the latest date per client, like this:

1   John Smith  GBP 2014-11-12
2   Jane Doe    USD 2014-12-23

How can I change my query to achieve this?

UPDATE Thanks to jarlh for the answer, however I have missed something - if there is a duplicate row - it will remain in the result, looking like this:

1   John Smith  GBP 2014-11-12
1   John Smith  GBP 2014-11-12
2   Jane Doe    USD 2014-12-23

Any way to make that work?

Upvotes: 0

Views: 50

Answers (3)

PP006
PP006

Reputation: 709

Try this,

Test Data:

CREATE TABLE #YourTable
(
    CLIENT_NO INT,
    CONTACT_NAME VARCHAR(20),
    CURRENCY VARCHAR(10),
    [DEAL_DATE] DATE
)

INSERT INTO #YourTable VALUES
(1,'John Smith','EUR','2014-10-07'),
(1,'John Smith','GBP','2014-11-12'),
(2,'Jane Doe','GBP','2014-09-17'),
(2,'Jane Doe','USD','2014-12-23'),
(1,'John Smith','USD','2013-11-13'),
(2,'Jane Doe','EUR','2012-09-06')

Query:

SELECT CLIENT_NO,CONTACT_NAME,CURRENCY,[DEAL_DATE]
FROM   (SELECT *,
               Row_Number()
                 OVER (
                   PARTITION BY CLIENT_NO
                   ORDER BY [DEAL_DATE] DESC) AS RN
        FROM   #YourTable)A
WHERE  RN = 1

Upvotes: 0

Arion
Arion

Reputation: 31249

You could do something like this:

Test data:

DECLARE @Deal TABLE(ClientNo INT,Currency VARCHAR(10),DealDate DATETIME)
DECLARE @Client TABLE(ClientNo INT,ContactName VARCHAR(100))

INSERT INTO @Deal
VALUES (1,'EUR','2014-10-07'),(1,'GBP','2014-11-12'),(2,'GBP','2014-09-17'),
(2,'USD','2014-12-23'),(1,'USD','2013-11-13'),(2,'EUR','2012-09-06')

INSERT INTO @Client
VALUES (1,'John Smith'),(2,'Jane Doe')

Query:

;WITH latestDeals
AS
(
    SELECT
        ROW_NUMBER() OVER(PARTITION BY ClientNo ORDER BY DealDate DESC) AS RowNbr,
        Deal.*
    FROM
        @Deal AS Deal
)
SELECT
    client.ClientNo,
    client.ContactName,
    latestDeals.Currency,
    latestDeals.DealDate
FROM
    @Client AS client
    JOIN latestDeals
        ON client.ClientNo=latestDeals.ClientNo
        AND latestDeals.RowNbr=1

Update:

If you want to use conventional query. You could do something like this:

SELECT
    client.ClientNo,
    client.ContactName,
    Latestdeal.maxDealDate as DealDate,
    deal.Currency
FROM
    @Client AS client
    JOIN 
    (
        SELECT
            MAX(Deal.DealDate) AS maxDealDate,
            Deal.ClientNo
        FROM
            @Deal AS Deal
        GROUP BY
            Deal.ClientNo
    ) AS Latestdeal
    ON client.ClientNo=Latestdeal.ClientNo
    JOIN @Deal as deal
        ON client.ClientNo=deal.ClientNo
        AND deal.DealDate=Latestdeal.maxDealDate

This will result in the same output

Result:

1   John Smith  GBP 2014-11-12 00:00:00.000
2   Jane Doe    USD 2014-12-23 00:00:00.000

Upvotes: 3

jarlh
jarlh

Reputation: 44795

Untested, but should work. Will return several rows for a clieant if the client has two (or more) deals the same, latest day.

SELECT      Client.ClientNo,
            Client.ContactName,
            Deal.Currency,
            Deal.DealDate
FROM        Deal
JOIN        Client ON Deal.ClientNo = Client.ClientNo
WHERE       Deal.DealDate = (select max(DealDate) from Deal
                             where ClientNo = Client.ClientNo)

Upvotes: 1

Related Questions