Mysql Groupby and Orderby problem

Question

Here is my data structure

when i try this sql

select rec_id, customer_id, dc_number, balance
from payments
where customer_id='IHS050018'
group by dc_number
order by rec_id desc;

something is wrong somewhere, idk

I need

rec_id  customer_id   dc_number   balance

2        IHS050018      DC3        -1
3        IHS050018      52         600 

I want the recent balance of the customer with respective to dc_number ?

Thanx

Answer 1

ORDER comes before GROUP:

select rec_id, customer_id, dc_number, balance
from payments
where customer_id='IHS050018'
order by rec_id desc
group by dc_number

Answer 2

I don't think there is a good way to do this in SQL without having window functions (like those in Postgres 8.4). You probably have to iterate over the dataset in your code and get the recent balances that way.

Answer 3

There are essentially two ways to get this

select p.rec_id, p.customer_id, p.dc_number, p.balance
from payments p
where p.rec_id IN (
    select s.rec_id
    from payments s
    where s.customer_id='IHS050018' and s.dc_number = p.dc_number
    order by s.rec_id desc
    limit 1);

Also if you want to get the last balance for each customer you might do

select p.rec_id, p.customer_id, p.dc_number, p.balance
from payments p
where p.rec_id IN (
    select s.rec_id
    from payments s
    where s.customer_id=p.customer_id and s.dc_number = p.dc_number
    order by s.rec_id desc
    limit 1);

What I consider essentially another way is utilizing the fact that select rec_id with order by desc and limit 1 is equivalent to select max(rec_id) with appropriate group by, in full:

select p.rec_id, p.customer_id, p.dc_number, p.balance
from payments p
where p.rec_id IN (
    select max(s.rec_id)
    from payments s
    group by s.customer_id, s.dc_number
    );

This should be faster (if you want the last balance for every customer), since max is normally less expensive then sort (with indexes it might be the same).

Also when written like this the subquery is not correlated (it need not be run for every row of the outer query) which means it will be run only once and the whole query can be rewritten as a join.

Also notice that it might be beneficial to write it as correlated query (by adding where s.customer_id = p.customer_id and s.dc_number = p.dc_number in inner query) depending on the selectivity of the outer query.

This might improve performance, if you look for the last balance of only one or few rows.