How to display highest value ID from duplicate orders

maximus1974
maximus1974 used Ask the Experts™
on
I need to display the WO_NUMBER with the highest IMAGE_KEY value. Would row_number with CTE accomplish this? If yes, what would be the proper syntax? If not, appreciate any suggestions.

SELECT * FROM (SELECT IMG.DATE_CREATED, WO_SUM.WORK_PERFORMED, WO_SUM.RELEASE_DATE, WO_STM.STM_AUTO_KEY, 
                         IMG.IMAGE_KEY, IMG.FILE_NAME, IMG.FILE_EXT, WO_SUM.WOO_AUTO_KEY, 
                         WO_SUM.WO_NUMBER, WO_SUM.WO_TYPE, WO_SUM.DEPT_NAME
FROM            BA_VIEW_WO_SUMMARY WO_SUM LEFT OUTER JOIN
                         WO_STM_COMPLETE WO_STM ON WO_SUM.WOO_AUTO_KEY = WO_STM.WOO_AUTO_KEY LEFT OUTER JOIN
                         IMAGE_LIST IMG ON WO_SUM.WOO_AUTO_KEY = IMG.SOURCE_PK
WHERE        WO_SUM.WO_TYPE = 'Internal' AND WO_SUM.WO_NUMBER LIKE 'WO%' AND WO_SUM.CLOSE_DATE IS NOT NULL
GROUP BY WO_SUM.WORK_PERFORMED, WO_SUM.RELEASE_DATE, WO_STM.STM_AUTO_KEY, 
                         IMG.IMAGE_KEY, IMG.FILE_NAME, IMG.DATE_CREATED, IMG.FILE_EXT, WO_SUM.WOO_AUTO_KEY, 
                         WO_SUM.WO_NUMBER, WO_SUM.WO_TYPE, WO_SUM.DEPT_NAME) WO_SUM2
order by WO_SUM2.wo_number,WO_SUM2.image_key

Open in new window

Capture.JPG
EXAMPLE_DATA.csv
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Distinguished Expert 2018
Commented:
I didn't set up a test case based on your CSV data because it didn't include table or column names.

You have a "group by" but no aggregate columns so I'm not sure why you are grouping.

To your question, yes row_number should work.

This is untested but may be close?

SELECT * FROM (
	SELECT IMG.DATE_CREATED, WO_SUM.WORK_PERFORMED, WO_SUM.RELEASE_DATE, WO_STM.STM_AUTO_KEY, 
                         IMG.IMAGE_KEY, IMG.FILE_NAME, IMG.FILE_EXT, WO_SUM.WOO_AUTO_KEY, 
                         WO_SUM.WO_NUMBER, WO_SUM.WO_TYPE, WO_SUM.DEPT_NAME
, row_number() over(partition by WO_SUM.WO_NUMBER order by IMG.IMAGE_KEY desc) rn
FROM            BA_VIEW_WO_SUMMARY WO_SUM LEFT OUTER JOIN
                         WO_STM_COMPLETE WO_STM ON WO_SUM.WOO_AUTO_KEY = WO_STM.WOO_AUTO_KEY LEFT OUTER JOIN
                         IMAGE_LIST IMG ON WO_SUM.WOO_AUTO_KEY = IMG.SOURCE_PK
WHERE        WO_SUM.WO_TYPE = 'Internal' AND WO_SUM.WO_NUMBER LIKE 'WO%' AND WO_SUM.CLOSE_DATE IS NOT NULL
) WO_SUM2
where rn=1
order by WO_SUM2.wo_number,WO_SUM2.image_key

Open in new window

Eduard GherguArchitect - Coder - Mentor

Commented:
Hi,
Your spec is not clear.
I need to display the WO_NUMBER with the highest IMAGE_KEY value
Does that mean that you want to display only that record? If there is more than one WO_NUMBER, do you want to display only the ones with the highest IMAGE_KEY? Can you be more descriptive?

Author

Commented:
Hi Eduard, yes, I only want to display the wo_numbers with the highest image key values.

Author

Commented:
Thank you slightvw. Sorry for the missing headers. This worked perfectly.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial