CODEWITHSUNDEEP
mysqlgroup-by
Deepu Sasidharan
Deepu Sasidharan

Reputation: 5309

Mysql latest record for distinct column

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..

Upvotes: 0

Views: 1507

Answers (5)

axiac
axiac

Reputation: 72177

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.

Upvotes: 0

Rushabh Makwana
Rushabh Makwana

Reputation: 71

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

Upvotes: 5

jafarbtech
jafarbtech

Reputation: 7015

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

Upvotes: 4

Lalabhai Patel
Lalabhai Patel

Reputation: 169

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

Upvotes: 2

Ullas
Ullas

Reputation: 11556

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

Upvotes: 1

Related Questions