I have a table with multiple rows. I want to have another table (identical structure) with just one row per key value (i.e. the row with the latest date).
Input data example
Red, 1/1/1906, Color is red
Red, 7/31/2006, Color is red
I only want one record per color. F
or Red this would be the 7/31/2006 record.
I thought if I ran code like below that I could sort the data so the highest date would be first. then if I loaded the results of that SQL into the new table with a unique constrainst then it would load the one with the highest date, ignoring subsequent values for color.
INSERT INTO COLORS_SINGLE (COLOR, EFFDT, DESCR)
SELECT COLOR, EFFDT, DESCR
WHERE EFF_STATUS = 'A'
ORDER BY COLOR, EFFDT desc, DESCR;
This does not work. it fails
Any suggestions? thanks