MySQL - Enumerate all rows within a minute and keep the first one

Question

I have a MySQL table with the following columns :

+----------------+-------------+------+-----+---------+----------------+
| Field          | Type        | Null | Key | Default | Extra          |
+----------------+-------------+------+-----+---------+----------------+
| id             | int(11)     | NO   | PRI | NULL    | auto_increment |
| date           | varchar(11) | NO   |     | NULL    |                |
| time           | varchar(12) | NO   |     | NULL    |                |
| gmt_offset     | varchar(2)  | YES  |     | NULL    |                |
| type           | varchar(10) | YES  |     | NULL    |                |
| yield_b        | varchar(10) | YES  |     | NULL    |                |
| yield_d        | varchar(10) | YES  |     | NULL    |                |
+----------------+-------------+------+-----+---------+----------------+

And I am trying to keep one row per minute (the first one of every minute) using the following query:

create table temp_table1 as
    select t.*
    from (select t1.*,
                 (@rn := if(@prevd <> date or minute(time) <> @prevm, 1,
                            if(@prevd := date, if(@prevm := minute(time), 1, 1), 1)
                           )
                 ) as seqnum
          from table1 t1 cross join
               (select @rn := 0, @prevd := 0, @prevm := 0) vars
          order by date, time
         ) t
     where seqnum = 1;


truncate table table1;

insert into table1(col1, . . ., coln)
    select col1, . . . , coln
    from temp_table1;

I try with this query to enumerate all rows within a minute, and then keep the first row for every minute.. But this doesn't work, it simply puts 1 in seqnum for every row, and does not get rid of any rows at all.

Can anybody help me make this query work and keep the first row of every minute?

Thanks in advance!

Answer 1

I don't know why the logic was inverted in the first if(). I think I was confused. Apologies for that.

create table temp_table1 as
    select t.*
    from (select t1.*,
                 (@rn := if(@prevd = date and minute(time) = @prevm, @rn + 1,
                            if(@prevd := date, if(@prevm := minute(time), 1, 1), 1)
                           )
                 ) as seqnum
          from table1 t1 cross join
               (select @rn := 0, @prevd := 0, @prevm := 0) vars
          order by date, time
         ) t
     where seqnum = 1;

The logic for the variable assignment in this case is:

• First clause does the comparisons and increments the @rn value.
• The next clauses assign the new values of the variables using nested if() calls, returning 1 for all possibilities.

The purpose of putting all the assignments in a single expression is not to create arbitrarily complicated SQL. MySQL does not guarantee the order of evaluation of expressions in a select. If you have multiple expressions with variable assignments, then they may evaluated in the wrong order.