When username changes on table register get only last username in mysql

Databases
1

Hi all,

This is my table tbl_register on MySQL database

+---------------------+------------------+---------------------+-----+
| sDateTime           | sRegisterAccount | sNewRegisterAccount | sID |
+---------------------+------------------+---------------------+-----+
| 2019-11-27 18:52:00 | 116019           | NULL                |   1 |
| 2017-08-05 14:01:00 | 030270           | 116019              |   2 |
| 2020-04-01 14:36:00 | 542116           | NULL                |   3 |
| 2018-10-30 18:36:00 | 000647           | 542116              |   4 |
| 2019-03-07 14:19:00 | 115286           | NULL                |   5 |
| 2020-02-17 23:16:00 | 170134           | NULL                |   6 |
+---------------------+------------------+---------------------+-----+

On this table tbl_register are also recorded any username changes in the column sNewRegisterAccount

e.g.

+---------------------+------------------+---------------------+-----+
| sDateTime           | sRegisterAccount | sNewRegisterAccount | sID |
+---------------------+------------------+---------------------+-----+
| 2020-04-01 14:36:00 | 542116           | NULL                |   3 |
| 2018-10-30 18:36:00 | 000647           | 542116              |   4 |
+---------------------+------------------+---------------------+-----+

in the column sRegisterAccount the old account value 000647

in the column sNewRegisterAccount the new account value 542116

I need get only last value of sNewRegisterAccount as return

+---------------------+------------------+---------------------+-----+
| sDateTime           | sRegisterAccount | sNewRegisterAccount | sID |
+---------------------+------------------+---------------------+-----+
| 2019-11-27 18:52:00 | 116019           | NULL                |   1 |
| 2020-04-01 14:36:00 | 542116           | NULL                |   3 |
| 2019-03-07 14:19:00 | 115286           | NULL                |   5 |
| 2020-02-17 23:16:00 | 170134           | NULL                |   6 |
+---------------------+------------------+---------------------+-----+

I’ve tried without success this query

mysql> SELECT
    A.*
FROM
    `tbl_register` A
INNER JOIN (
    SELECT
        sRegisterAccount,
        MAX(sDateTime) LastDatetimeForNode
    FROM
        `tbl_register`
    GROUP BY
        sRegisterAccount
) B ON A.sRegisterAccount = B.sRegisterAccount
AND A.sDateTime = B.LastDatetimeForNode;

+---------------------+------------------+---------------------+-----+
| sDateTime           | sRegisterAccount | sNewRegisterAccount | sID |
+---------------------+------------------+---------------------+-----+
| 2019-11-27 18:52:00 | 116019           | NULL                |   1 |
| 2017-08-05 14:01:00 | 030270           | 116019              |   2 |
| 2020-04-01 14:36:00 | 542116           | NULL                |   3 |
| 2018-10-30 18:36:00 | 000647           | 542116              |   4 |
| 2019-03-07 14:19:00 | 115286           | NULL                |   5 |
| 2020-02-17 23:16:00 | 170134           | NULL                |   6 |
+---------------------+------------------+---------------------+-----+
6 rows in set

what am I doing wrong?

please, can you help me?

expected results

+---------------------+------------------+---------------------+-----+
| sDateTime           | sRegisterAccount | sNewRegisterAccount | sID |
+---------------------+------------------+---------------------+-----+
| 2019-11-27 18:52:00 | 116019           | NULL                |   1 |
| 2020-04-01 14:36:00 | 542116           | NULL                |   3 |
| 2019-03-07 14:19:00 | 115286           | NULL                |   5 |
| 2020-02-17 23:16:00 | 170134           | NULL                |   6 |
+---------------------+------------------+---------------------+-----+

structure table tbl_register below

DROP TABLE IF EXISTS `tbl_register`;
CREATE TABLE `tbl_register` (
  `sDateTime` datetime DEFAULT NULL,
  `sRegisterAccount` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `sNewRegisterAccount` varchar(255) DEFAULT NULL,
  `sID` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`sID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of tbl_register
-- ----------------------------
INSERT INTO `tbl_register` VALUES ('2019-11-27 18:52:00', '116019', null, '1');
INSERT INTO `tbl_register` VALUES ('2017-08-05 14:01:00', '030270', '116019', '2');
INSERT INTO `tbl_register` VALUES ('2020-04-01 14:36:00', '542116', null, '3');
INSERT INTO `tbl_register` VALUES ('2018-10-30 18:36:00', '000647', '542116', '4');
INSERT INTO `tbl_register` VALUES ('2019-03-07 14:19:00', '115286', null, '5');
INSERT INTO `tbl_register` VALUES ('2020-02-17 23:16:00', '170134', null, '6');
2

did you try this –

SELECT sDateTime
     , sRegisterAccount
     , sID
  FROM tbl_register
 WHERE sNewRegisterAccount IS NULL
1 Like
3

Hi, many thanks for reply.

I’ve tried your suggestion and for this example of rows is working.

But I’ve find in tbl_register this case (surely there are others):

+---------------------+------------------+---------------------+-----+
| sDateTime           | sRegisterAccount | sNewRegisterAccount | sID |
+---------------------+------------------+---------------------+-----+
| 2016-02-24 11:37:33 | 052994           | 583725              |   7 |
+---------------------+------------------+---------------------+-----+

it should also extract this row… sorry… because the user have changed username from 052994 to 583725… but no longer authenticated with new username 583725

can you help me, please?

4

untested –

SELECT MAX(sDateTime) AS latest_datetime
     , this_acct
  FROM ( SELECT sDateTime
              , COALESCE(sNewRegisterAccount,sRegisterAccount) AS this_acct
              , sID
           FROM tbl_register
       ) AS data
GROUP
    BY this_acct
1 Like
5

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.