CODEWITHSUNDEEP
sqlselect
user1269625
user1269625

Reputation: 3209

SQL get latest record

Pretty sure my question headline is terrible, but anyways I have a table called invoices and inside that table each invoice has an id and each id had a client id so clients will have multiple invoices. I am trying to get latest invoices remainingbalance, The code I wrote doesnt work, can anyone tell me what I am doing wrong?

SELECT c.clientid,
    c.clientname,
    c.billingdate,
    (SELECT remainingbalance 
     FROM invoice i 
     WHERE i.client = c.clientid) AS remaining
FROM client c
ORDER BY clientname

Upvotes: 1

Views: 11546

Answers (3)

Prince Jea
Prince Jea

Reputation: 5690

Use INNER JOIN

Try this

UPDATE: I have revised my answer see if that works

 SELECT c.clientid,
 c.clientname,
 c.billingdate,
 d.remainingbalance
 FROM client c 
 INNER JOIN (
             SELECT  clientid,
             MAX(invoiceid) invoiceid
             FROM    invoice
             GROUP BY  clientid
             ) d
 ON c.clientid = d.clientid
 ORDER BY c.clientname

Upvotes: 5

Adriaan Stander
Adriaan Stander

Reputation: 166576

Off the bat, try something like

SELECT  c.clientid, 
        c.clientname, 
        c.billingdate, 
        (
            SELECT  TOP 1 
                    remainingbalance  
            FROM    invoice i  
            WHERE   i.client = c.clientid
            ORDER BY i.invoiceid DESC
        ) AS remaining 
FROM    client c 
ORDER BY    clientname

Which RDMS are you using?

A different approach would be as follows

SELECT  c.clientid,  
        c.clientname,  
        c.billingdate,
        rb.remainingbalance
FROM    client c  INNER JOIN
        (
            SELECT  i.clientid,
                    i.remainingbalance 
            FROM    invoice i INNER JOIN
                    (
                        SELECT  clientid,
                                MAX(invoiceid) invoiceid
                        FROM    invoice
                        GROUP BY    clientid
                    ) m ON  i.invoiceid = m.invoiceid
                        AND i.clientid = m.clientid
        ) rb    ON  c.clientid = rb.clientid
ORDER BY clientname

First get the "latest" invoice per client, then get the details required from that invoice and display with the client info.

Upvotes: 3

msEmmaMays
msEmmaMays

Reputation: 1063

Order by i.id DESC in your sub select

Upvotes: 0

Related Questions