Remove duplicate rows and keep only the latest worklog entries
P粉916760429
2023-08-10 17:25:56
<p>I created this query and it works. The problem is that it shows rows for each job log. I just need the latest work log for each event. </p>
<pre class="brush:php;toolbar:false;">SELECT DISTINCT
`HPD:Help Desk`.`Incident Number` AS Incident_Number,
`HPD:Help Desk`.`Status` AS Status,
`HPD:Help Desk`.`Priority` AS Priority,
`HPD:Help Desk`.`Assigned Group` AS Assigned_Group,
`HPD:Help Desk`.`Assignee` AS Assignee,
`HPD:Help Desk`.`Submit Date` AS Submit_Date,
`HPD:Help Desk`.`Last Modified Date` AS Last_Modified_Date,
`HPD:Help Desk`.`Description` AS Description,
`HPD:Help Desk`.`Submitter` AS Submitter,
`HPD:Search-Worklog`.`Worklog Description` AS Worklog_Description,
`HPD:Search-Worklog`.`Work Log ID` AS Work_Log_ID
FROM
`HPD:Help Desk`
INNER JOIN `HPD:Search-Worklog` on (`HPD:Search-Worklog`.`InstanceId` = `HPD:Help Desk`.`InstanceId`)
WHERE
(`HPD:Help Desk`.`Status` in ('Assigned','Pending','In Progress')
And `HPD:Help Desk`.`Submit Date` >= $__from/1000 and `HPD:Help Desk`.`Submit Date` <= $__to/1000)
ORDER BY
`HPD:Help Desk`.`Submit Date` ASC
LIMIT 20</pre>
<p>I have tried the HPD:Help Desk, HPD:Worklog and HPD:Search-Worklog forms. I also tried using a CASE statement to just display the latest/maximum worklog ID for each event, without success. I'm sure I'm missing something obvious/simple. </p>
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
Note: You do not need to use
select distinctrow_number() over()
. Usepartition by
in the over clause to control what triggers a row number reset, and useorder 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:when row numbers exist - try to avoid using it in general. Also, use table aliases yourself to simplify your code.