Mysql latest record for distinct column

Question

I have a table: invoice

inv_id    cus_id    due_amt    paid      total_due
1         71        300         0        300
2         71        200         0        500
3         71        NULL        125      375
4         72        50          0        50
5         72        150         0        200

I want the result

cus_id   total_due
71       375  
72       200

That is I want the total_due of unique customer or otherwise can say I need the latest invoice details of unique customer.

What I tried:

SELECT cus_id, total_due FROM invoice GROUP BY cus_id ORDER BY inv_id DESC

But this not give the required result.

Please someone can help me..

Answer 1

Try this Query :

SELECT `cus_id` as CustId, (SELECT `total_due` FROM invoice WHERE cus_id = CustId ORDER BY `inv_id` DESC LIMIT 1) as total_due FROM invoice GROUP BY cus_id

Answer 2

create a subquery to get the recent total_due of the customer

SELECT cus_id, (select total_due from invoice where inv_id=max(a.inv_id)) as total_due FROM invoice a GROUP BY cus_id ORDER BY inv_id DESC

Demo here

Answer 3

Try this sample query

SELECT i1.cus_id,i1.total_due FROM invoice as i1
LEFT JOIN invoice AS i2 ON i1.cus_id=i2.cus_id AND i1.inv_id<i2.inv_id
WHERE i2.inv_id IS NULL  

Answer 4

Just give a row number based on the group of cus_id and in the descending order of inv_id. Then select the rows having row number 1.

Query

select t1.cus_id, t1.total_due from (
    select cus_id, total_due, (
        case cus_id when @a 
        then @b := @b + 1 
        else @b := 1 and @a := cus_id end 
    ) as rn 
    from your_table_name t, 
    (select @b := 0, @a := '') r 
    order by cus_id, inv_id desc 
) t1 
where t1.rn = 1
order by t1.cus_id;

Find a demo here

Answer 5

The query:

SELECT cus_id, total_due FROM invoice GROUP BY cus_id ORDER BY inv_id DESC 

is invalid SQL because of the total_due column in the SELECT clause.

A query with GROUP BY is allowed to contain in the SELECT clause:

1. expressions that are also present in the GROUP BY clause;
2. expressions that use aggregate functions (aka "GROUP BY" functions);
3. columns that are functionally dependent on columns that are present in the GROUP BY clause.

The expression total_due is neither of the above.

Before version 5.7.5, MySQL used to accept such invalid queries. However, the server was free to return indeterminate values for the invalid expressions. Since version 5.7.5, MySQL rejects such queries (other RDBMSes reject them from long time ago...).

Why is such a query invalid?

Because a GROUP BY query does not return rows from the table. It creates the rows it returns. For each row it puts in the result set it uses a group of rows from the table. All rows in the group have the same values for the expressions present in the GROUP BY clause but they may have distinct values in the other expressions that appear in the SELECT clause.

What's the correct solution for this particular question?

I answered this question many times before on StackOverflow. Take a look at this answer, this answer, this answer or this answer and apply to your query what you learn from there.