P粉2524239062023-08-11 12:45:29
Assuming you are using a recent version of MySQL (or a variant thereof) that supports window functions, a very efficient way to get the "latest" is to use row_number() over()
. Use partition by
in the over clause to control what triggers a row number reset, and use order by
to control which rows in the partition get row number 1. Now, getting the "latest" requires a column in the worklog table to do this - I'm assuming there is a timestamp column (worklog_entry_date
) that exists in the table, You need to replace the column that actually determines "latest"). Nest the current query one level so that you can filter for row number 1 - these will be the "latest" rows:
Note: You do not need to use SELECT
*
FROM (
SELECT
h.`Incident Number` AS Incident_Number
, h.`Status` AS STATUS
, h.`Priority` AS Priority
, h.`Assigned Group` AS Assigned_Group
, h.`Assignee` AS Assignee
, h.`Submit Date` AS Submit_Date
, h.`Last Modified Date` AS Last_Modified_Date
, h.`Description` AS Description
, h.`Submitter` AS Submitter
, w.`Worklog Description` AS Worklog_Description
, w.`Work Log ID` AS Work_Log_ID
, row_number() over(partition by h.`Incident Number`
order by w.worklog_entry_date DESC) as rn
FROM `HPD: HELP Desk` h
INNER JOIN `HPD: Search - Worklog` w ON w.`InstanceId` = h.`InstanceId`
WHERE h.`Status` IN ('Assigned', 'Pending', 'In Progress')
AND h.`Submit Date` >= $_ _from / 1000
AND h.`Submit Date` <= $_ _to / 1000
) d
WHERE d.rn = 1
ORDER BY
d.`Submit Date` ASC
LIMIT 20
when row numbers exist - try to avoid using it in general. Also, use table aliases yourself to simplify your code.