CODEWITHSUNDEEP
mysql
Black
Black

Reputation: 4644

MySQL: Row counter for each unique date

I have a table with the following format

Date        | Text_Field
2014/12/03  | Hi
2014/12/03  | Hello
2014/12/03  | Bye
2014/12/03  | Ok
2014/12/03  | Yes
2014/12/04  | When 
2014/12/04  | Blah

I would like to get the table to appear as

Date | Text_Field
1    | Hi
1    | Hello
1    | Bye
1    | Ok
1    | Yes
2    | When 
2    | Blah

Here the row value will change once the date changes. I know that I should be using SELECT @row := @row + 1 FROM (row := 0) t somewhere in my query. However, I'm not sure how to get uniqueness of the dates as a condition on the counter. Is this possible in MySQL? If so, how should I go about this? Any tips would be appreciated.

Upvotes: 1

Views: 47

Answers (1)

M Khalid Junaid
M Khalid Junaid

Reputation: 64476

In other RDBMS it easier to achieve this kind of results by using window functions by Mysql don't have these functions available you can play with user defined variables one variable which will detect the change in date and another will increment the counter

 SELECT t.row_num,t.Text_Field
FROM (
SELECT *,
@r:= CASE WHEN @g = `Date` THEN @r  ELSE @r + 1 END row_num,
@g:= `Date`
FROM your_table 
CROSS JOIN(SELECT @g:= NULL,@r:=0) a
ORDER BY `Date`  
) t

Note ORDER BY Date is important in order to get the same sequence no. for repeated dates

Demo

Upvotes: 1

Related Questions