mysql select ordernumber by group

I'm trying to do something like 'select groupwise maximum', but I'm looking for groupwise order number.

so with a table like this

briefs
----------
id_brief | id_case | date
1        | 1       | 06/07/2010
2        | 1       | 04/07/2010
3        | 1       | 03/07/2010
4        | 2       | 18/05/2010
5        | 2       | 17/05/2010
6        | 2       | 19/05/2010

I want a result like this

breifs result
----------
id_brief | id_case | dateOrder
1        | 1       | 3
2        | 1       | 2
3        | 1       | 1
4        | 2       | 2
5        | 2       | 1
6        |开发者_开发技巧 2       | 3

I think I want to do something like described here MySQL - Get row number on select, but I don't know how I would reset the variable for each id_case.

This will give you how many records are there with this id_case value and a date less than or equal to this date value.

SELECT t1.id_brief,
    t1.id_case,
    COUNT(t2.*) AS dateOrder
FROM yourtable AS t1
    LEFT JOIN yourtable AS t2 ON t2.id_case = t1.id_case AND t2.date <= t1.date
GROUP BY t1.id_brief

Mysql is permissive about columns which can be queries using GROUP BY. With a more stric DBMS you may need GROUP BY t1.id_brief, t1.id_case.

I strongly advise you to have the right indexes on the table:

CREATE INDEX filter1 ON yourtabl (id_case, date)