mysql get distinct row where column has latest date-CodePudding

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?

CodePudding user response：

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.

CodePudding user response：

If you're on MySQL v8, you can combine