How to select a max value in a group in SQL

Question

My goal is to only have 1 row per part number/color combo. I tried using the max function however it doesn't seem to be working right, it will still give me duplicate values:

Here is my current code:

SELECT DISTINCT

TRIM(PART_NO) AS PART_NO, 
TRIM(PART_CLR_CD) AS COLOR, 
MAX(EFCT_DT) AS EFCT_DT,
STD_CURRENCY,
STD_COST
    
FROM HSP.12

--where part_no like '181655MR A0%'

GROUP BY PART_NO,
PART_CLR_CD, 
STD_CURRENCY,
STD_COST

I am trying to select the most recent data per part. I have looked through other peoples concerns regarding MAX() in SQL but I cant find a solution that works for me. I only have 1 data source I am pulling from for more context. My table should look something like the image here

Answer 1

Table value functions Lateral or cross apply depending on RDMBS is the modern way of doing this. The old school way of doing this was to use a query to get the max date per part and then inner join it to the base data set thereby giving you just max date per part record.

In the below the sub query aliased "Sub" does this and then joins back to your base set getting 1 record per part. (assuming date can't be repeated for a part)

SELECT
TRIM(PART_NO) AS PART_NO, 
TRIM(PART_CLR_CD) AS COLOR, 
EFCT_DT AS EFCT_DT,
STD_CURRENCY,
STD_COST
    
FROM HSP.12
INNER JOIN 
   (SELECT MAX(EFCT_DT) maxDate, Part_No FROM HSP.12 GROUP BY Part_No) sub
 on sub.MaxDate = HSP.12.EFCT_DT 
and sub.Part_No = HSP.12.Part_No
--where part_no like '181655MR A0%'

Answer 2

Use ROW_NUMBER() OVER PARTITION to get the row with highest EFCT_DT for each number/color combo.

select *
from
  (SELECT PART_NO, PART_CLR_CD AS COLOR, EFCT_DT, STD_CURRENCY, STD_COST
          ROW_NUMBER() OVER (partition by PART_NO, PART_CLR_CD order by EFCT_DT desc) rowno
   FROM HSP.12) dt
where rowno = 1

Edit: SQL-92 compliant answer - expected to work on any dbms:

SELECT DISTINCT TRIM(PART_NO) AS PART_NO, 
                TRIM(PART_CLR_CD) AS COLOR, 
                EFCT_DT,
                STD_CURRENCY,
                STD_COST
FROM HSP.12 h1
WHERE NOT EXISTS (SELECT * FROM HSP.12 h2
                  WHERE h2.PART_NO = h1.PART_NO
                    AND h2.PART_CLR_CD = h1.PART_CLR_CD
                    AND h2.EFCT_DT > h1.EFCT_DT)

(DISTINCT is probably not needed.)