Question based on my previous topic . Operations with the same table.

CREATE TABLE IF NOT EXISTS `discrete_archive` ( `id` int(11) NOT NULL AUTO_INCREMENT, `dtime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, `f1` smallint(6) NOT NULL, `f2` smallint(6) NOT NULL, `f3` smallint(6) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=2 ;

It is necessary to select the dtime and, for example, f1 fields from the table, excluding the consecutive rows with the same f1 values ​​from the sample.

For example. We have a table:

 dtime f1 10:00 1 10:01 2 10:02 4 10:03 4 10:04 2 10:05 7

You must select the following:

 10:00 1 10:01 2 10:02 4 10:04 2 10:05 7

Those. record (10:03 4) is not needed, because she repeats the previous one.

If in table 1 entry, then you must return it.
If in table 0 records, then it is necessary to return empty selection.

This is a somewhat simplified formulation of the problem. It is really necessary to compare the values ​​of f1 by multiplying by some kind of mask (meaning logical multiplication or conjunction). For example, take the mask 0x10. Then

 (3 & 0x10) == (2 & 0x10)

Those. we have a table

 dtime f1 10:00 1 10:01 2 10:02 4 10:03 4 10:04 2 10:05 7

It is necessary to select the following by mask 0x10:

 10:00 (f1 & 0x10) = 0 10:01 (f1 & 0x10) = 2 10:02 (f1 & 0x10) = 0 10:04 (f1 & 0x10) = 2

    2 answers 2

    KEEP BIKE

     SELECT dtime, f1 FROM (Select discrete_archive.id, discrete_archive.dtime, discrete_archive.f1, IFNULL(t1.f1,discrete_archive.f1-1) as ff1 FROM discrete_archive LEFT JOIN (Select id, f1 FROM discrete_archive) as `t1` on discrete_archive.id=t1.id+1 ) as t2 WHERE f1!=ff1 
       select distinct(f1), dtime from 'table'
      • This will not work - for example, the series 1,2,3,4,2,3,5 - you will get 12345, but should get 1234235. - Ale_x
      • Thanks for the answer, but unfortunately, this is not what you need. It is necessary to apply DISTINCT only to the elements going in a row. The sample from the first example should be 10:00 1 10:01 2 10:02 4 10:04 2 10:05 7 select distinct (f1), dtime from 'table' will return 10:00 1 10:01 2 10:02 4 10:05 7 - dzukp
      • Well, then choose in order, choose the first, save the values, and when choosing the next, check the value. - Diefair
      • It is necessary to do everything using MySQL in one request. - dzukp
      • Do you have each field has a unique number? - Diefair