Get last data for contracts

Question

I want to select last information about client's balance from MySQL's database. I wrote next script:

    SELECT *
FROM
    (SELECT        
                    contract_balance.cid,
                    /*contract_balance.yy,
                    contract_balance.mm,*/
                    contract_balance.expenses, 
                    contract_balance.revenues, 
                    contract_balance.expenses + contract_balance.revenues AS total,
                    (CAST(CAST(CONCAT(contract_balance.yy,'-',contract_balance.mm,'-01')AS CHAR) AS DATE)) AS dt
            FROM contract_balance

            /*WHERE                 
                CAST(CAST(CONCAT(contract_balance.yy,'-',contract_balance.mm,'-01')AS CHAR) AS DATE) < '2013-11-01'
            LIMIT 100*/

    ) AS tmp
    WHERE tmp.dt = (
                        SELECT MAX(b.dt)
                        FROM tmp AS b
                        WHERE tmp.cid = b.cid      
                    )

But server return:

Table 'clientsdatabase.tmp' doesn't exist

How to change this code for get required data?

Answer 1

Try this one in your subquery you are trying to get the MAX of (CAST(CAST(CONCAT(contract_balance.yy,'-',contract_balance.mm,'-01')AS CHAR) AS DATE)) AS dt but in subquery your aliased table tmp doesn't exist so the simplest way you can do is to calculate the MAX of dt and use GROUP BY contract_balance.cid contractor id ,i guess it will fullfill your needs

SELECT        
contract_balance.cid,
contract_balance.expenses, 
contract_balance.revenues, 
contract_balance.expenses + contract_balance.revenues AS total,
MAX((CAST(CAST(CONCAT(contract_balance.yy,'-',contract_balance.mm,'-01')AS CHAR) AS DATE))) AS dt
FROM contract_balance
GROUP BY  contract_balance.cid

Answer 2

Try this:

SELECT * 
FROM (SELECT cb.cid, cb.expenses, cb.revenues, cb.expenses + cb.revenues AS total, 
            (CAST(CAST(CONCAT(cb.yy,'-',cb.mm,'-01')AS CHAR) AS DATE)) AS dt
      FROM contract_balance cb ORDER BY dt DESC
     ) AS A 
GROUP BY A.cid