CODEWITHSUNDEEP
sql
user1269625
user1269625

Reputation: 3209

SQL Max get the highest id

I got these query string here...

SELECT 
    c.clientid, c.clientname, c.billingdate, 
    i.total - (select ifnull(sum(p.amount), 0) from payment p 
               where p.invoice = i.invoiceid Order by i.invoiceid) AS remaining 
FROM 
    client c 
INNER JOIN 
    invoice i 
WHERE 
    c.clientid = i.client 
ORDER BY 
    clientname

What it does, is that it gets a list of all the client, their billing date and their remaining balance. I also have invoices for clients and each of the invoices have a remaining balance. What I am trying to do is grab the clients latest remaining balance (the invoice with the highest id value) per a client.

What the query I have does it list each client multiple times with each remaining balance per an invoice... like so

client 1 0.00
client 1 1.00
client 1 3.00

What I am looking for is something like is

client 1 0.00
client 2 3.00
client 3 5.00

I hope it makes sense what I want to do. I have also tried Order by MAX(i.invoiceid) but that only returns one client.

Upvotes: 0

Views: 271

Answers (2)

hol
hol

Reputation: 8423

I am not sure why the max invoiceid did not work for you

SELECT 
    c.clientid, c.clientname, c.billingdate, 
    i.total - (select ifnull(sum(p.amount), 0) from payment p 
               where p.invoice = i.invoiceid Order by i.invoiceid) AS remaining 
FROM 
    client c 
INNER JOIN 
    invoice i 
WHERE 
    c.clientid = i.client 
AND i.invoiceid = (SELECT MAX(i2.invoiceid) FROM invoice i2 WHERE i2.client = i.client) 
ORDER BY 
    clientname

Now you should only get the clients latest invoice

Upvotes: 1

Parallelis
Parallelis

Reputation: 719

Try this :

SELECT c.clientid, c.clientname, c.billingdate, i.total - 
(select ifnull(sum(p.amount), 0) from payment p where p.invoice = i.invoiceid Order by i.invoiceid DESC LIMIT 1) AS remaining 
FROM client c inner join invoice i where c.clientid = i.client ORDER BY clientname

with the DESCending order and LIMIT 1, you will only select ONE amount per client, the amount with the highest id (and thus the latest)

Upvotes: 0

Related Questions