Return Results Based on Max Of Another Field

[Ohioste]

Ok, so I'll first admit that I'm not a great SQL writer. I can get by with the basics and am learning more every day.

I haven't had to do this in while.

Here's my current code:

SELECT CHCASN Carton_#, max(CEDIVT) Divert_Location, max(CECRTS) , max(CDTBPU) Units, CDSTYL||CDSSFX||CDCOLR SKU, max(trim(CDAREA)||trim(CDAISL)||trim(CDBAY)||trim(CDLEVL)||trim(CDPOSN)) Pick_Location

 FROM ((CHCART00 INNER JOIN CDCART00 ON CHCASN=CDCASN) LEFT OUTER JOIN CECVIF00 ON (CHWHSE=CEWHSE) AND (CHCASN=CECASN)) INNER JOIN WDWAVE00 ON CHPRWV=WDPRWV

 WHERE  (CHSTAT>'03' AND CHSTAT<'25') AND CHMIS2='' AND CHWHSE='TGL' AND (WDDOWK=1 OR WDDOWK=9)

GROUP BY CHCASN, CDSTYL||CDSSFX||CDCOLR

 ORDER BY CHCASN

What I need to do is keep my current grouping but instead of max(CEDIVT), I need to take CEDIVT for the most recent date/time of CECRTS.

Any help would be greatly appreciated. I've looked a possible solutions online but the issue is making the data work for my code and I just don't know how to translate it.

Thanks in advance.

 

[TamarGranor]

The basic idea is that you use a derived table (or a CTE) to figure out which record you want for each item, and then join with that derived table to pull out the other data.

It's too hard to understand what your data looks like from what you posted, but I just posted a similar example in the thread "Return most recent records from table."

If you can't figure it out from there, show the structure of your table or tables.

Tamar