How to use query results in another query?

Question

I am trying to write a query which will give me the last entry of each month in a table called transactions. I believe I am halfway there as I have the following query which groups all the entries by month then selects the highest id in each group which is the last entry for each month.

SELECT max(id),
EXTRACT(YEAR_MONTH FROM date) as yyyymm
FROM transactions
GROUP BY yyyymm

Gives the correct results

id   yyyymm
100  201006 
105  201007 
111  201008 
118  201009 
120  201010

I don’t know how to then run a query on the same table but select the balance column where it matches the id from the first query to give results

id    balance   date
120   10000     2010-10-08
118   11000     2010-09-29

I've tried subqueries and looked at joins but i'm not sure how to go about using them.

Answer 1

You can make your first select an inline view, and then join to it. Something like this (not tested, but should give you the idea):

SELECT x.id
     , t.balance
     , t.date
  FROM your_table t
       /* here, we make your select an inline view, then we can join to it */
     , (SELECT max(id) id,
        EXTRACT(YEAR_MONTH FROM date) as yyyymm
        FROM transactions
        GROUP BY yyyymm) x
 WHERE t.id = x.id