MySQL Forums
Forum List  »  Newbie

How to select all parent row and most recent/last child row only
Posted by: Duncan Hill
Date: August 03, 2022 08:11AM

I've created two tables:

trap_detail
+----------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+-------------+------+-----+---------+-------+
| trap_id | varchar(20) | NO | PRI | NULL | |
| application_id | tinytext | NO | | NULL | |
| dev_eui | tinytext | NO | | NULL | |
| dev_addr | tinytext | NO | | NULL | |
| latitude | double | YES | | 0 | |
| longitude | double | YES | | 0 | |
+----------------+-------------+------+-----+---------+-------+

trap_events
+----------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+-------------+------+-----+---------+-------+
| trap_id | varchar(20) | NO | PRI | NULL | |
| trap_datetime | datetime | NO | PRI | NULL | |
| trap_triggered | tinyint | NO | | 0 | |
+----------------+-------------+------+-----+---------+-------+

I'm trying & failing to create a SQL script that selects all records from the parent (trap_detail) sorted by trap_id and a single most recent row from the child (trap_events) sorted by trap_datetime.

I've done a number of searches and struggling to find a example that achieves what I require.

Thank you in advance for any help or suggestions.

Options: ReplyQuote


Subject
Written By
Posted
How to select all parent row and most recent/last child row only
August 03, 2022 08:11AM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.