CODEWITHSUNDEEP
mysqlsql
Developer
Developer

Reputation: 26243

mysql sql query to group data with addition column

I have following data

id  Date
-----------------
1   1/1/2009
2   1/1/2009
3   1/1/2009
4   20/1/2009
5   24/1/2009
6   29/1/2009
7   29/1/2009
8   29/1/2009
9   10/2/2009
10  11/2/2009
11  21/2/2009
12  21/2/2009
13  21/2/2009

I would like to return the result with something like this. 

id   date     issue (this column is not in db, addition of column in query using logic)
----------------------------------
1   1/1/2009        1/1/2009
2   1/1/2009        1/1/2009
3   1/1/2009        1/1/2009
4   20/1/2009       1/1/2009
5   24/1/2009       1/1/2009
6   29/1/2009       29/1/2009
7   29/1/2009       29/1/2009
8   29/1/2009       29/1/2009
9   10/2/2009       29/1/2009
10  11/2/2009       29/1/2009
11  21/2/2009       21/2/2009
12  21/2/2009       21/2/2009
13  21/2/2009       21/2/2009
  1. I would like to read DATE column to find bulk* set of records (i.e more than 3 records with same date) I mean records on same date
  2. Once I find the bulk* record set I want to keep adding the first bulk record's date in ISSUE column till it hits next bulk records set.

*in DATE COLUMN of above example, first bulk records are with id 1,2,3 and second set of bulk records are with id 6,7,8 and vise versa...

I want to repeat step 1 and 2 over and over again untill it reaches the end of table.

Is there any way of doing this mysql sql query?

Upvotes: 1

Views: 160

Answers (2)

valex
valex

Reputation: 24144

select id,date,
if((@s:=if(date=(select date from t t2 where t2.id>t.id order by id limit 1 offset 0)
           and
           date=(select date from t t2 where t2.id>t.id order by id limit 1 offset 1)
          ,date,@s))='',date,@s) issue
from t,(select @s:='') s
order by id

I've used 2 nested queries (offset 0 and 1) because you need set of 3 records if you need only 2 records with the same date to start bulk set just leave only first one.

Also for the first records before the first bulk set starts I set the same DATE for the ISSUE column.

Here is a SQLFiddle example

Upvotes: 1

Omesh
Omesh

Reputation: 29101

try this query, It's tested working for your data set:

SELECT IF(b.cnt < 3, @d_prev, a.`date`) AS new_date,
       IF(b.cnt = 3, @d_prev := a.`date`, a.`date`) AS date
FROM table_name a
     INNER JOIN (SELECT `date`, COUNT(1) AS cnt 
                 FROM table_name, (SELECT @d_prev := "0000-00-00") a 
                 GROUP BY d
                ) b
                ON a.`date` = b.`date`
ORDER BY a.id ASC;

Upvotes: 0

Related Questions