Doolius Doolius - 4 months ago 7
SQL Question

Get other data from row based on Max value

CREATE TABLE #TmpTbl
(
PurchaseId INT
,UserID INT
,InvoiceName VARCHAR(20)
,PaymentDue DATE
,PaymentMade DATE
)

INSERT INTO #TmpTbl
SELECT 1
,1
,'Ef Fee'
,'2016-01-01'
,'2016-01-02'
UNION ALL
SELECT 2
,1
,'Monthly Pmt'
,'2016-05-01'
,'2016-05-02'
UNION ALL
SELECT 3
,1
,'Ef Fee'
,'2016-07-26'
,NULL

PurchaseId UserID InvoiceName PaymentDue PaymentMade
1 1 EF Fee 2016-01-01 2016-01-02
2 1 Monthly Pmt 2016-05-01 2016-05-02
3 1 EF Fee 2016-07-26 NULL


Sorry for the crappy title.

What I'm needing is to get the
MAX(PaymentDue)
for each user, for invoices that are named
Ef Fee
. Then I need to get the
PaymentMade
value (and usually some other columns) based on whatever row is selected. It would be nice to be able to get the
PurchaseId
for the row, but not required. The way I've done this in the past is with multiple CTE's. Grabbing the
UserId, InvoiceName, MAX(PaymentDue)
then matching back on all 3 to grab the other data needed, but I'm wondering if there's a better/quicker way to accomplish this without having to use multiple cte's.

Desired result would be row 3 because it is the most recent
PaymentDue
.

EDIT:

PurchaseId UserID InvoiceName PaymentDue PaymentMade
3 1 EF Fee 2016-07-26 NULL

Answer

Assuming I'm understanding your question correctly, here's one option using row_number:

select *
from (
    select *, row_number() over (partition by userid order by paymentdue desc) rn
    from #TmpTbl
    where invoicename = 'EF Fee'
) t
where rn = 1
Comments