limit results to most recent date

Applications Grid / Reports
#1

I have a grid I need to make work. I need to limit the records to return only the most recent date for each record. Example data is:

1, 20, 2018-02-03, pass
2, 30, 2018-02-04, pass
3, 20, 2017-10-05, pass
4, 30, 2017-10-06, pass
5, 20, 2017-01-08, pass
6, 30, 2017-01-09, fail
7, 20 , 2016-08-17, pass
8, 21, 2016-06-06, pass

SQL

SELECT
    pftID,
    pfttrainID,
    pftDate,
    pftpass,
    pftnotes
FROM
    tbl_pft

I only want the grid to return the following lines.

1, 20, 2018-02-03, pass
2, 30, 2018-02-04, pass
8, 21, 2016-06-06, pass

I have tried using:

ORDER BY pftDate DESC
LIMIT 1

it gives me 1 result only

I am still having issues with sc macro and the best way to use them.

Thanks

#2

depends on your database system ( Server)

one version is :

select Distinct P.pftID , D.pfttrainID, D.pftDate, D.pftpass, D.pftnotes from tbl_pft P
Inner Join (SELECT Top1 pftID, pfttrainID, pftDate, pftpass, pftnotes FROM tbl_pft order by pftDate desc) D on D.pftID = P.pftID

#3

I’m using MySQL. I don’t understand this sql Statement

You are using P.pftID then D.pftID

This seems overly complicated. can you point me to the PHP or MySQL manual that talks about this?

#4

I was playing around with this a little and found this solution to work.


SELECT
   pfttrainID,
   pftID,
   pftDate, 
   pftpass, 
   pftnotes
FROM
   tbl_pft s1
WHERE pftDate = (SELECT MAX(pftDate) FROM tbl_pft s2 WHERE s1.pfttrainID = s2.pfttrainID)
#5

mySQL is a liitle bit more complex :wink:

here is your querystring :

select t3.pftID, t1.pfttrainID, t2.latestDate,t3.pftpass
from
(select pfttrainID, MAx(pftDate)as latestDate from tbl_pft group by pfttrainID) t2
inner join (select distinct pfttrainID from tbl_pft) t1
on t1.pfttrainID = t2.pfttrainID
inner join tbl_pft t3 on t3.pfttrainID = t2.pfttrainID and t3.pftDate = t2.latestDate

t1 ,t2, t3 are for the subquerys to get the correct values from