Is there a more efficient way of returning records that match the max value?

I have a table with fields of interest:

Order      Shift    DateTime
23605624   Red      43470.23958
23605624   Yellow   43469.73958
23605624   Green    43469.38542
23605917   Red      43471.26042
23605917   Green    43471.73958
23605953   Red      43470.23958
23605953   Yellow   43469.17083

I want [Order] and [Shift] where [DateTime] is the largest value for each [Order]. So the above table would translate to:

Order      Shift    DateTime
23605624   Red      43470.23958
23605917   Green    43471.73958
23605953   Red      43470.23958

Though I don’t need the DateTime field.

I have actually achieved this but the method feels overly convoluted (and takes a considerable time for the query to run) I have subqueries in both the from clause and one of the joins.

It feels like I should be able to do some kind of join in the where clause along the lines of “value=max(value) but I just can’t seem to figure it out as the subquery already has a join and also includes a case statement and I can’t seem to master the syntax.

This is the complete code I have:

SELECT
   max_datetime.[Order],
   max_datetime.[Line],
   max_datetime.[Quantity],
   max_datetime.[UoM],
   shifts.[Shift]

FROM (
   SELECT
      SCMPEH.PRONUM as [Order],
      SCMPEH.LINNUM as [Line],
      SUM(SCMPEH.PEHQTY) as [Quantity],
      SCMPEH.PEHUNT as [UoM],

      MAX(CASE
         WHEN SCMPEH.PEHSTT < '05:45:00'
         THEN SCMPEH.PEHDAT + SCMPEH.PEHSTT + 1
         ELSE SCMPEH.PEHDAT + SCMPEH.PEHSTT
      END) as [DateTime]

   FROM
      SCMPEH

      INNER JOIN SCMLIN on
         (SCMLIN.LINNUM = SCMPEH.LINNUM)

      INNER JOIN SCMSHI on
         (SCMSHI.SHINUM = SCMPEH.SHINUM)

   WHERE (
      SCMPEH.PEHDAT >= '1/1/2019'
   ) AND (
      SCMLIN.LINEXP = 'Y'
   )

   GROUP BY
      SCMPEH.LINNUM,
      SCMPEH.PRONUM,
      SCMPEH.PEHUNT

   ) as max_datetime

INNER JOIN (
   SELECT
      SCMPEH.PRONUM as [Order],
      SCMSHI.SHIDES as [Shift],

      CASE
         WHEN SCMPEH.PEHSTT < '05:45:00'
         THEN SCMPEH.PEHDAT + SCMPEH.PEHSTT + 1
         ELSE SCMPEH.PEHDAT + SCMPEH.PEHSTT
      END as [DateTime]

   FROM
      SCMPEH

      INNER JOIN SCMSHI on
         (SCMSHI.SHINUM = SCMPEH.SHINUM)

   WHERE (
      SCMPEH.PEHDAT >= '1/1/2019'
   )
   ) as shifts on
   (shifts.[Order] = max_datetime.[Order]) AND
   (shifts.[DateTime] = max_datetime.[DateTime])

What I’m most interested in is the [Shift] field.

Answer

One option is to use WITH TIES in concert with row_number()

Example

Select top 1 with ties * 
 From  YourTable
 Order By Row_Number() over (Partition By [Order] Order By DateTime desc)

Returns

Order       Shift   DateTime
23605624    Red     43470.23958
23605917    Green   43471.73958
23605953    Red     43470.23958

Full Disclosure: Using a sub-query with row_number() is a nudge more performant