Question 113586 in Mysql Question posted in
The official MySQL documentation can be found here.

mysql get distinct row where column has latest date

I have a table of entries in a mysql table:

script      lastRun     processed
________________________________
scriptA     1663695633  0
scriptB     1663695647  1
scriptA     1663695776  0
scriptB     1663695799  1
scriptC     1663695950  1
scriptA     1663695957  0
scriptB     1663695959  1

I am trying to get 1 entry for each script that is the last date run.

the results from the above I am trying to get are:

scriptC     1663695950  1
scriptA     1663695957  0
scriptB     1663695959  1

I have tried using

select distinct script,lastRun,processed from table order by lastRun;

but i get all entries.

I have tried using

select script,lastRun,processed from table group by script order by lastRun asc;

but i dont get the latest run for each.

what query can i use to achieve my goal?

Tags:

3

Answers


  1. 0 
    SELECT Script, lastRun, processed 
FROM table t1 
WHERE lastRun = (Select MAX(LastRun) FROM table t2 where t2.script = t1.script)
Order by lastrun

    I usually work with MS SQL, so the syntax for mysql might be slightly different, but essentially you do a subquery to find what the last run time was for that script and only pull that.

    Login or Signup to reply.
  2. 0

    If you’re on MySQL v8, you can combine common table expression (cte) with ROW_NUMBER() function as such:

    WITH cte AS (
  SELECT script, lastRun, processed, 
         ROW_NUMBER() OVER (PARTITION BY script ORDER BY lastRun DESC) Rn
     FROM testtable)
SELECT script, lastRun, processed
 FROM cte 
  WHERE Rn=1
ORDER BY lastRun;

    Demo fiddle

    Login or Signup to reply.
  3. 0

    Please try this:

    SELECT script,MAX(lastrun) AS lastrun,processed
FROM scripting
GROUP BY script,processed
ORDER BY lastrun,processed

    If we run it, It returns:

    FER

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search