CODEWITHSUNDEEP
mysql
bart2puck
bart2puck

Reputation: 2522

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?

Upvotes: 0

Views: 44

Answers (3)

Ozan Sen
Ozan Sen

Reputation: 2615

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

Upvotes: 0

FanoFN
FanoFN

Reputation: 7114

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

Upvotes: 0

Charis Grubb
Charis Grubb

Reputation: 36

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.

Upvotes: 2

Related Questions