Select the last position of the same row in MySQL

Hi all, i need your help.

The version of MySQL database is 5.5.62

This is my table

+-----+---------------------+----------+--------------+
| sId | sDateHour           | sElement | sStatus      |
+-----+---------------------+----------+--------------+
|   1 | 2020-01-01 01:54:40 | 044572   | Registered   |
|   2 | 2020-01-01 02:02:11 | 035219   | Unregistered |
|   3 | 2020-01-01 02:36:01 | 044572   | Unregistered |
|   4 | 2020-01-01 02:36:06 | 020523   | Registered   |
|   5 | 2020-01-01 02:36:15 | 020523   | Unregistered |
|   6 | 2020-01-01 03:05:22 | 065164   | Unregistered |
|   7 | 2020-01-01 03:05:51 | 086113   | Registered   |
|   8 | 2020-01-01 03:49:09 | 086113   | Unregistered |
|   9 | 2020-01-01 03:58:58 | 070328   | Unregistered |
|  10 | 2020-01-01 04:15:15 | 065570   | Unregistered |
|  11 | 2020-01-01 04:27:29 | 045473   | Registered   |
|  12 | 2020-01-01 04:40:36 | 074000   | Unregistered |
|  13 | 2020-01-01 04:40:43 | 045473   | Unregistered |
|  14 | 2020-01-01 05:00:50 | 070245   | Unregistered |
|  15 | 2020-01-01 06:17:53 | 079107   | Registered   |
+-----+---------------------+----------+--------------+

i need to select the last position (field sDateHour) of the same row value sElement when the sStatus value is Unregistered

i need this return

+-----+---------------------+----------+--------------+
| sId | sDateHour           | sElement | sStatus      |
+-----+---------------------+----------+--------------+
|   2 | 2020-01-01 02:02:11 | 035219   | Unregistered |
|   3 | 2020-01-01 02:36:01 | 044572   | Unregistered |
|   5 | 2020-01-01 02:36:15 | 020523   | Unregistered |
|   6 | 2020-01-01 03:05:22 | 065164   | Unregistered |
|   8 | 2020-01-01 03:49:09 | 086113   | Unregistered |
|   9 | 2020-01-01 03:58:58 | 070328   | Unregistered |
|  10 | 2020-01-01 04:15:15 | 065570   | Unregistered |
|  12 | 2020-01-01 04:40:36 | 074000   | Unregistered |
|  13 | 2020-01-01 04:40:43 | 045473   | Unregistered |
|  14 | 2020-01-01 05:00:50 | 070245   | Unregistered |
+-----+---------------------+----------+--------------+

e.g.

In this case in table i have the same element 044572 value

+-----+---------------------+----------+--------------+
| sId | sDateHour           | sElement | sStatus      |
+-----+---------------------+----------+--------------+
|   1 | 2020-01-01 01:54:40 | 044572   | Registered   |
|   3 | 2020-01-01 02:36:01 | 044572   | Unregistered |
+-----+---------------------+----------+--------------+

the sId number no. 3 is newer (last position) than sId no. 1 and the value of sElement is Unregistered

i need extract this row

And I have tried this solution without success because the set is empty

mysql> SELECT
	*
FROM
	`tbl_elements` A
INNER JOIN (
	SELECT
		sStatus,
        sElement,
		MAX(sDateHour) LastDatetimeForElements
	FROM
		`tbl_elements`
	WHERE
		sStatus = 'Unregistered'
) B ON A.sElement = B.sElement
AND A.sDateHour = B.LastDatetimeForElements;
Empty set

How to do resolve this?

Please, any suggestion

My table below

-- ----------------------------
-- Table structure for tbl_elements
-- ----------------------------
DROP TABLE IF EXISTS `tbl_elements`;
CREATE TABLE `tbl_elements` (
  `sId` int(11) NOT NULL AUTO_INCREMENT,
  `sDateHour` datetime DEFAULT NULL,
  `sElement` varchar(255) DEFAULT NULL,
  `sStatus` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`sId`)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of tbl_elements
-- ----------------------------
INSERT INTO `tbl_elements` VALUES ('1', '2020-01-01 01:54:40', '044572', 'Registered');
INSERT INTO `tbl_elements` VALUES ('2', '2020-01-01 02:02:11', '035219', 'Unregistered');
INSERT INTO `tbl_elements` VALUES ('3', '2020-01-01 02:36:01', '044572', 'Unregistered');
INSERT INTO `tbl_elements` VALUES ('4', '2020-01-01 02:36:06', '020523', 'Registered');
INSERT INTO `tbl_elements` VALUES ('5', '2020-01-01 02:36:15', '020523', 'Unregistered');
INSERT INTO `tbl_elements` VALUES ('6', '2020-01-01 03:05:22', '065164', 'Unregistered');
INSERT INTO `tbl_elements` VALUES ('7', '2020-01-01 03:05:51', '086113', 'Registered');
INSERT INTO `tbl_elements` VALUES ('8', '2020-01-01 03:49:09', '086113', 'Unregistered');
INSERT INTO `tbl_elements` VALUES ('9', '2020-01-01 03:58:58', '070328', 'Unregistered');
INSERT INTO `tbl_elements` VALUES ('10', '2020-01-01 04:15:15', '065570', 'Unregistered');
INSERT INTO `tbl_elements` VALUES ('11', '2020-01-01 04:27:29', '045473', 'Registered');
INSERT INTO `tbl_elements` VALUES ('12', '2020-01-01 04:40:36', '074000', 'Unregistered');
INSERT INTO `tbl_elements` VALUES ('13', '2020-01-01 04:40:43', '045473', 'Unregistered');
INSERT INTO `tbl_elements` VALUES ('14', '2020-01-01 05:00:50', '070245', 'Unregistered');
INSERT INTO `tbl_elements` VALUES ('15', '2020-01-01 06:17:53', '079107', 'Registered');

you forgot the GROUP BY in the subquery

SELECT tbl.*
  FROM ( SELECT sElement
              , MAX(sDateHour) LastDatetimeForElements
           FROM tbl_elements
          WHERE sStatus = 'Unregistered'
         GROUP
             BY sElement ) latest 
INNER
  JOIN tbl_elements tbl
    ON tbl.sElement = latest.sElement
   AND tbl.sDateHour = latest.LastDatetimeForElements;
2 Likes