Mysql – Select latest record for each ID

greatest-n-per-groupgroup byMySQLselect

I have three tables that contains data as below:

Users

Id   Name     Other_Columns
---------------------------
1    John     Blah
2    Ricky    Blah
3    Stella   Blah
4    Bob      Blah

Saldo

Id   User_id   Saldo
--------------------
1    3         0.00
2    1         9.00
3    2         0.15
4    4         3.50

Payments

Id   User_id   Amount     Paid_date
------------------------------------------
1    2         10.00      2014-09-01 08:10
2    2         25.00      2014-09-01 09:00
3    3         100.00     2014-05-10 12:47
4    1         20.50      2014-02-23 15:30

How to get result like this:

Id   Name     Saldo     Last Payment
------------------------------------------
1    John     9.00      23.02.2014 20.50
2    Ricky    0.15      01.09.2014 25.00
3    Stella   0.00      0000-00-00 0.00
4    Bob      3.50      10.05.2014 100.00

Thank you.

Best Answer

;with [Users](Id,Name,Other_Columns)
AS(
    SELECT 1,'John','Blah' UNION ALL
    SELECT 2,'Ricky','Blah' UNION ALL
    SELECT 3,'Stella','Blah' UNION ALL
    SELECT 4, 'Bob','Blah')
,
Saldo (Id,[User_id],Saldo)
AS
    (SELECT 1,    3 ,       0.00 UNION ALL
    SELECT 2,    1,        9.00 UNION ALL
    SELECT 3,    2,         0.15 UNION ALL
    SELECT 4,    4,         3.50 )

,Payments(Id,[User_id],Amount,Paid_date)
AS
    (SELECT 1,2,10.00,'2014-09-01 08:10' UNION ALL
    SELECT 2,2,25.00,'2014-09-01 09:00' UNION ALL
    SELECT 3,4,100.00,'2014-05-10 12:47' UNION ALL
    SELECT 4,1,20.50,'2014-02-23 15:30')


SELECT
    ID
    ,Name
    ,Saldo
    ,[Last Payment]
FROM
(
    SELECT
        U.ID,U.Name,S.Saldo 
        ,LEFT(COALESCE(P.Paid_date,'0000-00-00'),10)  + ' ' + CAST( COALESCE(Amount,0) AS VARCHAR(50))  [Last Payment]
        ,ROW_NUMBER() OVER(PARTITION BY U.ID ORDER BY COALESCE(P.Paid_date,'0000-00-00') DESC) AS RN
        FROM
            [Users] AS U
            INNER JOIN Saldo AS S
            ON U.ID= S.[User_id]
            left JOIN Payments AS P
            ON P.[User_id] = U.ID
)X
WHERE X.RN =1

output:

ID  Name    Saldo   Last Payment
1   John    9.00    2014-02-23 20.50
2   Ricky   0.15    2014-09-01 25.00
3   Stella  0.00    0000-00-00 0.00
4   Bob 3.50    2014-05-10 100.00
Related Question