Remove duplicate rows and keep only the latest worklog entries
P粉916760429
P粉916760429 2023-08-10 17:25:56
0
1
640
<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>
P粉916760429
P粉916760429

reply all(1)
P粉252423906

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:

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
Note: You do not need to use

select distinct

when row numbers exist - try to avoid using it in general. Also, use table aliases yourself to simplify your code.

Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template
About us Disclaimer Sitemap
php.cn:Public welfare online PHP training,Help PHP learners grow quickly!