This topic is locked

how to select the max date

11/2/2019 8:13:18 AM
PHPRunner General questions
C
chrispa author

good day
i have a report with using following sql
SELECT

tblrequisition.OrderConfRefNo,

tblrequisition.OurReferNo,

tblordersupplydetails.SupplierLink,

YEAR(tblordersupplydetails.orderdate) AS Year,

MONTHNAME(tblordersupplydetails.orderdate) AS MONTH,

MONTH(tblordersupplydetails.orderdate) AS MONTHSORT,

tblship.visible,

tblordersupplydetails.orderdate,

tblship.FullName,

tblordersupplydetails.Curr,

SUM(tblordersupplydetails.ForeignTotal) AS FOREIGNTOTAL,

SUM(tblordersupplydetails.USDTotal) AS USDTOTAL,

tblordersupplydetails.VesselDept,

tblordersupplydetails.Description

FROM tblordersupplydetails

INNER JOIN tblrequisition ON tblordersupplydetails.EnquryLink = tblrequisition.IdCounter

INNER JOIN tblship ON tblrequisition.VesselLink = tblship.ShipCode

WHERE (tblrequisition.OurReferNo ='ATH-SUPPLY-434') AND (tblship.visible =1)

GROUP BY tblrequisition.OrderConfRefNo, tblrequisition.OurReferNo, tblordersupplydetails.SupplierLink, tblordersupplydetails.Description

ORDER BY tblship.FullName, tblrequisition.OrderConfRefNo
and it returns following results
image link
but how to select only these records with max datetime from orderdate i.e. these values with orderdate 2019-09-06 15:34:20
thanks in advance for your help

HJB 11/2/2019



good day
i have a report with using following sql
SELECT

tblrequisition.OrderConfRefNo,

tblrequisition.OurReferNo,

tblordersupplydetails.SupplierLink,

YEAR(tblordersupplydetails.orderdate) AS Year,

MONTHNAME(tblordersupplydetails.orderdate) AS MONTH,

MONTH(tblordersupplydetails.orderdate) AS MONTHSORT,

tblship.visible,

tblordersupplydetails.orderdate,

tblship.FullName,

tblordersupplydetails.Curr,

SUM(tblordersupplydetails.ForeignTotal) AS FOREIGNTOTAL,

SUM(tblordersupplydetails.USDTotal) AS USDTOTAL,

tblordersupplydetails.VesselDept,

tblordersupplydetails.Description

FROM tblordersupplydetails

INNER JOIN tblrequisition ON tblordersupplydetails.EnquryLink = tblrequisition.IdCounter

INNER JOIN tblship ON tblrequisition.VesselLink = tblship.ShipCode

WHERE (tblrequisition.OurReferNo ='ATH-SUPPLY-434') AND (tblship.visible =1)

GROUP BY tblrequisition.OrderConfRefNo, tblrequisition.OurReferNo, tblordersupplydetails.SupplierLink, tblordersupplydetails.Description

ORDER BY tblship.FullName, tblrequisition.OrderConfRefNo
and it returns following results
image link
but how to select only these records with max datetime from orderdate i.e. these values with orderdate 2019-09-06 15:34:20
thanks in advance for your help


https://stackoverflow.com/questions/29283303/select-max-date-by-grouping
... for inspiration purposes ...