it should also extract the row number 7, because the user have changed username from
052994 to 583725… but no longer authenticated with new username
583725
structure tabletbl_registerbelow
DROPTABLEIFEXISTS`tbl_register`;CREATETABLE`tbl_register`(`sDateTime` datetime DEFAULTNULL,`sRegisterAccount` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULTNULL,`sNewRegisterAccount` varchar(255)DEFAULTNULL,`sID` int(11)NOTNULL AUTO_INCREMENT,PRIMARYKEY(`sID`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;-- ------------------------------ Records of tbl_register-- ----------------------------INSERTINTO`tbl_register`VALUES('2019-11-27 18:52:00','116019',null,'1');INSERTINTO`tbl_register`VALUES('2017-08-05 14:01:00','030270','116019','2');INSERTINTO`tbl_register`VALUES('2020-04-01 14:36:00','542116',null,'3');INSERTINTO`tbl_register`VALUES('2018-10-30 18:36:00','000647','542116','4');INSERTINTO`tbl_register`VALUES('2019-03-07 14:19:00','115286',null,'5');INSERTINTO`tbl_register`VALUES('2020-02-17 23:16:00','170134',null,'6'); INSERTINTO`tbl_register`VALUES('2016-02-24 11:37:33','052994','583725','7');
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
WHERE
A.sNewRegisterAccount is null or
not exists (select 1 from `tbl_register` E
where E.sRegisterAccount = A.sNewRegisterAccount)
Member
49 Points
77 Posts
When username changes on table register get only last username in mysql
May 28, 2020 08:32 AM|Chevy Marl Sunderland|LINK
Hi all,
This is my table
tbl_register
on MySQL databaseOn this table
tbl_register
are also recorded any username changes in the columnsNewRegisterAccount
e.g.
in the column
sRegisterAccount
the old account value000647
in the column
sNewRegisterAccount
the new account value542116
I need get only last value of
sNewRegisterAccount
as returnI've tried without success this query
what am I doing wrong?
please, can you help me?
expected results
it should also extract the row number 7, because the user have changed username from 052994 to 583725… but no longer authenticated with new username 583725
structure table
tbl_register
belowMember
310 Points
76 Posts
Re: When username changes on table register get only last username in mysql
May 28, 2020 12:34 PM|gapimex|LINK
Hi,
Try:
Hope this help