0

In my table below, I have id and job_id fields with some of the job_id fields being duplicates. I want to get the MAXIMUM value of the id field corresponding to each unique job_id.

I have now tried the GROUP_BY clause, but it is not giving me the right result.

For instance,

SELECT MAX(id) AS max_id, job_id
FROM job_status_update
GROUP BY job_id

would produce:

enter image description here

But from the first image, the id of 6 from the job_status_update table corresponds to job_status_id of 3 and not 1 So I want to write a query to get just these fields: max_id, job_id

Please can someone give me a pointer on what to do? Thank you!

enter image description here

4
  • That query does not include job_status_id; fix it.
    – Rick James
    Nov 23 '21 at 16:44
  • You need some technique involving "groupwise-max"; it cannot be done in a query as simple as what you have.
    – Rick James
    Nov 23 '21 at 16:45
  • I see you've updated your question now. Originally you were not asking for the job_status_id to be returned (as your question still says "I want to write a query to get just these fields: max_id, job_id'). I'll update my query when I get a chance to return the correct job_status_id for your latest row per grouping. To get the latest row you can use a window function, and the query becomes a little more complicated, but is definitely doable. Please update your post's tags with which version and implementation of MySQL you're using, as that will affect the answer now as well.
    – J.D.
    Nov 23 '21 at 18:04
  • @ErnestElikem Please see my updated answer, with an updated dbfiddle, demonstrating how to achieve what you're actually looking for. Please note you must be on at least MySQL version 8.0 to utilize this solution (please updated your post's tags with which version of MySQL you're on, as I previously requested).
    – J.D.
    Nov 24 '21 at 2:17
0

Since you really wanted the whole row (or at least other fields of the row itself) besides the max id per job_id, you can accomplish this with a window function like ROW_NUMBER() like so:

-- only latest row per job_id
SELECT id AS max_id, job_id, job_status_id --, updated_by, date_added, last_updated
FROM
(
  SELECT id, job_id, job_status_id, updated_by, date_added, last_updated,
    ROW_NUMBER() OVER (PARTITION BY job_id ORDER BY id DESC) AS GroupSortId
  FROM job_status_update
) Results
WHERE GroupSortId = 1 -- returns only the latest row per grouping of job_id

This will group up all duplicate job_ids and return only the max row of each grouping, thus resulting in a distinct result set of job_ids, their correlating max_id and the correct correlating job_status_id. If you want the other fields as well, please remove the comment in the outer-query's SELECT list.

Please see this dbfiddle for a working example.

5
  • It did not work. Please using the group by clause still results in not getting the actual max_id value. It simply returns the first row that from the group by clause. I am trying to do a sorting first and then do a grouping and see if it would work. Nov 23 '21 at 13:11
  • @ErnestElikem Please update your question with the exact query you're using, the results you're getting, and your expected results. Based on what you described my above query should get what you're looking for.
    – J.D.
    Nov 23 '21 at 13:13
  • @ErnestElikem Also please see the working example with my query that I just linked at the bottom of my answer.
    – J.D.
    Nov 23 '21 at 13:52
  • Your query works fine. But there is a possibility that the jobs are not updated in the same order that you have demonstrated in your DB-Fiddle. The problem is with the group by clause. Even though it prevents duplicates, it results in getting only the first job_status_id that was obtained from the GROUP BY clause. So though what you have presented is good, in the real-world (at least for my application), things would not work that way. Nov 23 '21 at 14:20
  • @ErnestElikem I've updated the dbfiddle to insert the jobs data in a different order to show that it doesn't matter what order the data is inserted into the table, you still get the same results. Please re-open and re-run the dbfiddle to see that. If you want to re-order the results after getting the max_id then please let me know with more details on how you want the results to be ordered, but the data of the results themselves is correct (regardless of how and when your data is inserted / updated in your table). It always returns the max id. This is true for your application as well.
    – J.D.
    Nov 23 '21 at 15:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.