SQL: Select first row of duplicates (RESOLVED)

JamieHoffmann · ‎12-01-2020

Edit: I was able to accomplish my objective by summarizing my field, joining it back to the original dataset and selecting my desired rows that way. Thank you everyone for responding to my post.

Happy December everyone!

Could anyone suggest me a chunk of SQL code to use in Select by Attributes to select only the first row of each group of duplicates? Or select a duplicate that associated with another column's max value?

Objective:

I have a table of 520,000 census block codes that include broadband download speeds. I have the table sorted by Census Blocks (Ascending) and then by download speeds (Descending) to list the census blocks in the order of highest download speed to the lowest (each group of duplicate census blocks are ordered from highest to lowest download speeds).

Because there are duplicates of each census block, I only want to select the census block with the highest download speed. I have the table sorted where the first census block code of each group of duplicates has the highest download speed.

My goal is to select each block code with the highest download speed. The data is stored in a file geodatabase

Example:

Block Code	Download Speed
1234 (row to select)	25
1234	2
1234	2
5678 (row to select)	100
5678	2

Thanks,

Jamie

Edit: I do not have access to Delete Identical/Find Identical Tools

JoshuaBixby · ‎09-22-2021

Since you are using Oracle, the following should or might work:

WHERE ID IN (
    SELECT MIN(ID) as ID
    FROM APP_ARCHIVE.RX_INCIDENT_DEVICES_ARCHIVE
    GROUP BY INCIDENT_DEVICE_ID
)