0

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?

bart2puck
  • 2,432
  • 3
  • 27
  • 53
  • [Retrieving the last record in each group - MySQL](https://stackoverflow.com/questions/1313120/retrieving-the-last-record-in-each-group-mysql) – id'7238 Sep 21 '22 at 06:16

3 Answers3

2
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.

Charis Grubb
  • 36
  • 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

FanoFN
  • 6,815
  • 2
  • 13
  • 33
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

Ozan Sen
  • 2,477
  • 2
  • 4
  • 16