mysql select ordernumber by group

Question

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.

You want to do a rank(order by date partition by id_case) from oracle. Look at http://onlamp.com/pub/a/mysql/2007/03/29/emulating-analytic-aka-ranking-functions-with-mysql.html?page=1 — Cyril Gandon, Mar 28 '11 at 09:45

vbence · Accepted Answer · 2011-03-28T09:46:54.200

2

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)

edited Mar 28 '11 at 09:46

answered Mar 28 '11 at 09:41

vbence

20,084
9
69
118

If I change <= to >= it gives me the order I want. Well done giving the correct answer to an ambiguous question ;) – Matt Mar 28 '11 at 09:49

mysql select ordernumber by group

1 Answers1