Hi,
I need update the existing value on column rowNumber on the table t3 using a database MySql version 8.0.17
On the table t3 are stored two rows for each date... and I need this return... that is to associate value 1 to the date with the oldest id and value 2 to the date with the most recent id...
+-----------+------------+-----+
| rowNumber | data | sID |
+-----------+------------+-----+
| 2 | 2019-04-30 | 22 |
| 1 | 2019-04-30 | 21 |
| 2 | 2019-04-29 | 20 |
| 1 | 2019-04-29 | 19 |
| 2 | 2019-04-28 | 18 |
| 1 | 2019-04-28 | 17 |
| 2 | 2019-04-27 | 16 |
| 1 | 2019-04-27 | 15 |
| 2 | 2019-04-26 | 14 |
| 1 | 2019-04-26 | 13 |
| 2 | 2019-04-25 | 12 |
| 1 | 2019-04-25 | 11 |
| 2 | 2019-04-24 | 10 |
| 1 | 2019-04-24 | 9 |
| 2 | 2019-04-23 | 8 |
| 1 | 2019-04-23 | 7 |
| 2 | 2019-04-22 | 6 |
| 1 | 2019-04-22 | 5 |
| 2 | 2019-04-21 | 4 |
| 1 | 2019-04-21 | 3 |
| 2 | 2019-04-20 | 2 |
| 1 | 2019-04-20 | 1 |
+-----------+------------+-----+
Any suggestion?
My table structure below, thanks in advance for any help.
-- ----------------------------
-- Table structure for t3
-- ----------------------------
DROP TABLE IF EXISTS `t3`;
CREATE TABLE `t3` (
`rowNumber` int(10) NOT NULL,
`data` date NULL DEFAULT NULL,
`sID` int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`sID`) USING BTREE,
INDEX `data`(`data`) USING BTREE
) ENGINE = MyISAM;
-- ----------------------------
-- Records of t3
-- ----------------------------
INSERT INTO `t3` VALUES (3852, '2019-04-30', 22);
INSERT INTO `t3` VALUES (3851, '2019-04-30', 21);
INSERT INTO `t3` VALUES (3850, '2019-04-29', 20);
INSERT INTO `t3` VALUES (3849, '2019-04-29', 19);
INSERT INTO `t3` VALUES (3848, '2019-04-28', 18);
INSERT INTO `t3` VALUES (3847, '2019-04-28', 17);
INSERT INTO `t3` VALUES (3846, '2019-04-27', 16);
INSERT INTO `t3` VALUES (3845, '2019-04-27', 15);
INSERT INTO `t3` VALUES (3844, '2019-04-26', 14);
INSERT INTO `t3` VALUES (3843, '2019-04-26', 13);
INSERT INTO `t3` VALUES (3842, '2019-04-25', 12);
INSERT INTO `t3` VALUES (3841, '2019-04-25', 11);
INSERT INTO `t3` VALUES (3840, '2019-04-24', 10);
INSERT INTO `t3` VALUES (3839, '2019-04-24', 9);
INSERT INTO `t3` VALUES (3838, '2019-04-23', 8);
INSERT INTO `t3` VALUES (3837, '2019-04-23', 7);
INSERT INTO `t3` VALUES (3836, '2019-04-22', 6);
INSERT INTO `t3` VALUES (3835, '2019-04-22', 5);
INSERT INTO `t3` VALUES (3834, '2019-04-21', 4);
INSERT INTO `t3` VALUES (3833, '2019-04-21', 3);
INSERT INTO `t3` VALUES (3832, '2019-04-20', 2);
INSERT INTO `t3` VALUES (3831, '2019-04-20', 1);