topic Re: SQL: Select first row of duplicates (RESOLVED) in ArcGIS Pro Questions

SQL: Select first row of duplicates (RESOLVED)

JamieHoffmann — Tue, 01 Dec 2020 18:06:50 GMT

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

Re: SQL: Select first row of duplicates

DanPatterson — Tue, 01 Dec 2020 16:02:44 GMT

Find Identical (Data Management)—ArcGIS Pro | Documentation

you can use a single or multiple fields to determine how identical, identical is

Re: SQL: Select first row of duplicates

JamieHoffmann — Tue, 01 Dec 2020 16:05:39 GMT

I forgot to mention that I do not have the license for that tool. Thanks for the reply!

Re: SQL: Select first row of duplicates

Zeke — Tue, 01 Dec 2020 16:12:03 GMT

For SQL, you can may also be able to use SELECT DISTINCT [field] FROM [table] ORDER BY ID. Adjust to your situation.

Re: SQL: Select first row of duplicates

JoshuaBixby — Tue, 01 Dec 2020 16:34:41 GMT

Regarding

@JamieHoffmann wrote:

I have a table of 1000 internet providers. Lets say three rows are listed Comcast, Comcast, Comcast. I only want to select the first of the three that appears in the table.

I think it is better stated that you only want to select one of them, not selecting the "first" because there is no first if the records are truly duplicates. If you want to select first based on OID, then it would be important to state that.

The SQL that is supported with the Select By ... tools varies depending on back-end data store. What is the back-end data store, file geodatabase?

Re: SQL: Select first row of duplicates

JamieHoffmann — Tue, 01 Dec 2020 16:50:26 GMT

You are right about that which I do not clear up. I did not fully clarify my 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 lowest (each group of duplicate census blocks are ordered from highest to lowest download speeds).

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 be selected)	25
1234	2
1234	2
5678 (row to be selected)	100
5678	2

Re: SQL: Select first row of duplicates (RESOLVED)

Zeke — Tue, 01 Dec 2020 18:47:19 GMT

Definitely test this before relying on it, but in my response above, replace ID by Download Speed in the ORDER BY clause.
There are other SQL methods, such as TOP(n), subqueries, etc., but been a while since I've used it much and don't want to mislead you.

Re: SQL: Select first row of duplicates (RESOLVED)

LauraGiboo — Tue, 21 Sep 2021 14:26:19 GMT

I have a similar issue. I have several records per Incident Device ID and only want the first (numerical order) device per Incident ID.

In the image attached, I have 3 incident device numbers (375444, 375445, and 375449). I want to select the first record of these using the ID field. I have no idea how to structure this query. Any help is greatly appreciated!

Re: SQL: Select first row of duplicates (RESOLVED)

JoshuaBixby — Tue, 21 Sep 2021 17:11:36 GMT

@LauraGiboo, as I mention in an earlier comment, the SQL that is supported with the Select By ... tools varies depending on back-end data store. What is the back-end data store, file geodatabase?

Re: SQL: Select first row of duplicates (RESOLVED)

LauraGiboo — Tue, 21 Sep 2021 18:10:02 GMT

It is an enterprise geodatabase (oracle).

Re: SQL: Select first row of duplicates (RESOLVED)

JoshuaBixby — Wed, 22 Sep 2021 21:27:33 GMT

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 )