I want to select all rows from my table but also contain the which value for each row in that row's creation day.
More detailed: "1st record of that day, 2nd record of that day".
For example, assume I have this table with rows:
name | created_at
-----------------
john | 2013-02-04 10:00
bob | 2013-02-04 14:00
dale | 2013-02-05 09:00
lucy | 2013-02-06 11:00
sean | 2013-02-06 13:00
sal | 2013-02-06 18:00
So when I select all these values, I need results as:
name | created_at | which_record
---------------------------------------
john | 2013-02-04 10:00 | 1
bob | 2013-02-04 14:00 | 2
dale | 2013-02-05 09:00 | 1
lucy | 2013-02-06 11:00 | 1
sean | 2013-02-06 13:00 | 2
sal | 2013-02-06 18:00 | 3
Is there a way to get this which_record values without selecting the values and calculating again for each record or complicated subqueries?
I can either select all and generate this which_record per record or I can select previous records count for each record but I'm worried if running this subquery for each row would slow down the response.
Any alternatives or suggestions?
Try this:
SELECT id, name, created_at,
IF(@prev = date(created_at), @s:=@s+1, @s:=1) AS daily_order,
@prev:=date(created_at) as dummy
FROM table1, (SELECT @s:= 0, @prev:='') s
ORDER BY created_at
Working demo: http://sqlfiddle.com/#!2/d3ad43/1
Check this on your data to see the performance impact.