Ruben rj Ruben rj - 2 months ago 6
SQL Question

how to show max date for primary key in sql server

with bookingdetails as (
select booking_master.booking_datetime as newdatetime,
booking_master.ETA as neweta
from booking_master where booking_ID='143'
)


The output for above is:

newdatetime neweta
2016-10-02 03:00:00.000 2016-10-05 03:00:00.000


Than I run this:

with comparetrucklog as (
select truck_log.truck_id as bookedtruck,
truck_log.ETA as eta
from truck_log
where (select newdatetime from bookingdetails) between truck_log.pickupdate and truck_log.ETA
or (select neweta from bookingdetails) between truck_log.pickupdate and truck_log.ETA
)


In the result for
comparetrucklog
in bookedtruck column 37 is repeated twice but I need to display only the maximum datetime in
eta
.

enter image description here

How can I achieve this?

Answer

why simple max() will work right?

 select bookedtruck, max(eta) from bookingmaster group by bookedtruck
Comments