CODEWITHSUNDEEP
mysql
Chevy Mark Sunderland
Chevy Mark Sunderland

Reputation: 435

Select the last position of the same row in MySQL

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');

Solution

DEMO

Upvotes: 1

Views: 63

Answers (3)

forpas
forpas

Reputation: 164064

Use a correlated subquery which returns the max sDateHour for each sElement:

select t.*
from tbl_elements t 
where t.sStatus = 'Unregistered'
and t.sDateHour = (select max(sDateHour) from tbl_elements where sElement = t.sElement and sStatus = t.sStatus)

See the demo.
Results:

> 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

Upvotes: 1

isaace
isaace

Reputation: 3429

You need to add GROUP BY for your inner query

 SELECT
*
FROM
`tbl_elements` A
INNER JOIN (
SELECT
    sStatus,
    sElement,
    MAX(sDateHour) LastDatetimeForElements
FROM
    `tbl_elements`
WHERE
    sStatus = 'Unregistered'
    GROUP BY sStatus, sElement  <----
) B ON A.sElement = B.sElement
AND A.sDateHour = B.LastDatetimeForElements;

Upvotes: 0

ScaisEdge
ScaisEdge

Reputation: 133360

You need GROUP BY sStatus, sElement

SELECT  A.*
FROM  `tbl_elements` A
INNER JOIN (
    SELECT
        sStatus,
        sElement,
        MAX(sDateHour) LastDatetimeForElements
    FROM
        `tbl_elements`
    WHERE sStatus = 'Unregistered'
    GROUP BY sStatus,
        sElement,
) B ON A.sElement = B.sElement
    AND A.sDateHour = B.LastDatetimeForElements
        AND A.sStatus = B.sStatus

and the join on all the column for right matching

Upvotes: 1

Related Questions