MySQL Combine multiple rows

I have a table similar to the following (of course with more rows and fields):

category_id | client_id | date        | step
     1            1       2009-12-15    first_step
     1            1       2010-02-03    last_step

     1            2       2009-04-05    first_step
     1            2       2009-08-07    last_step
     
     2            3       2009-11-22    first_step
     
     3            4       2009-11-14    first_step
     3            4       2010-05-09    last_step

I would like to transform this so that I can calculate the time between the first and last steps and eventually find the average time between first and last steps, etc. Basically, I'm stumped at how to transform the above table into som开发者_运维百科ething like:

category_id | first_date | last_date
     1        2009-12-15   2010-02-03
     1        2009-04-05   2009-08-07
     2        2009-11-22   NULL
     3        2009-11-14   2010-05-09

Any help would be appreciated.

Updated based on question update/clarification:

  SELECT t.category_id,
         MIN(t.date) AS first_date,
         CASE 
           WHEN MAX(t.date) = MIN(t.date) THEN NULL 
           ELSE MAX(t.date)
         END AS last_date
    FROM TABLE t
GROUP BY t.category_id, t.client_id

a simple GROUP BY should do the trick

SELECT   category_id
         , MIN(first_date)
         , MAX(last_date)
    FROM TABLE
GROUP BY category_ID

Try:

select
    category_id
    , min(date) as first_date
    , max(date) as last_date
from
    table_name
group by
    category_id
    , client_id

You need to do two sub queries and then join them together - something like the following

select * from (select *, date as first_date from table where step = "first_step") a left join ( select * date as last_date from table where step = "lastt_step") b on (a.category_id = b.category_id)

Enjoy!

simple answer:

  SELECT fist.category_id, first.date, last.date
    FROM tableName first
    LEFT JOIN tableName last
        ON first.category_id = last.category_id
        AND first.step = 'first_step'
        AND last.step ='last_step'

You could also do the calculations in the query instead of just returning the two date values.