MySQL Forums
Forum List  »  Newbie

Sorting joined table results chronologically to return most recent entry
Posted by: Shannon Whitty
Date: September 24, 2018 12:57AM

I have 3 Database Tables:

- OpenROHeader
- OpenROData
- OpenRONotes

Header & Data are updated (overwritten) hourly while Notes are added manually and linked via a field "RO_Number"

What I want the SQL to do is retrieve the most current "Notes" (by 'date_updated')

SELECT file_date, rod.id as id, key_id, key_name, key_value, DATE_FORMAT(expected_close_date , '%d/%m/%y' ) as expected_close_date, wty_dept
FROM OpenROHeader roh
JOIN OpenROData rod ON roh.id=rod.header_id
LEFT JOIN OpenRONotes ron ON rod.key_value=ron.ro_number
WHERE roh.customer_id='193'
GROUP BY key_id, key_name

There are 2 entries in Notes table - I want the most recent:

id, customer_id, ro_number, expected_close_date, advisor_notes, wty_dept, date_updated
4059, 193, 'S117986', NULL, 'WTY_ON<br>- S.W.', 'on', '2018-09-24 05:02:45'
4060, 193, 'S117986', NULL, 'WTY_OFF<br>- S.A.', NULL, '2018-09-24 05:03:24'

I want it to return data (last 2 columns) in SELECT from row with id 4060 NOT 4059

http://sqlfiddle.com/#!9/f00326/1/0

But instead of returning most current 'wty_dept' (NULL) from 2018-09-24 05:03:24

My SQL statement is returning the previous entry 'on' from 2018-09-24 05:02:45 (older)

How can I sort a join to return most recent data

Options: ReplyQuote


Subject
Written By
Posted
Sorting joined table results chronologically to return most recent entry
September 24, 2018 12:57AM


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.