nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Any tools that allow &quot;GROUP BY&quot; or &quot;DISTINCT&quot; in queries on file gdb data

HarryBowman

I'm not 100% clear which tools on which data support which query clauses. I think none will support GROUP BY or DISTINCT on file gdb, but I'd be happy to be proven incorrect. I am exporting 36 million records to get my data out of file gdb and into something that will support it.

Find more posts tagged with

Accepted answers

LanceShipman

What version of ArcGIS are you using? Support for DISTINCT was added at 10.0 and for GROUP BY at 10.1. There are no GP Tools that supports DISTINCT and GROUP BY with the file geodatabase. You need to use ArcObjects (IQueryDef).

All comments

HarryBowman

What version of ArcGIS are you using? Support for DISTINCT was added at 10.0 and for GROUP BY at 10.1. There are no GP Tools that supports DISTINCT and GROUP BY with the file geodatabase. You need to use ArcObjects (IQueryDef).

I am on 10.0 right now. OK, If I need to, I will investigate ArcObjects. My solution yesterday was to dump the data to text file and setting up the query via Access. Worked fine and speedily once the data was exported.

HarryBowman

In Select by Attributes, can do a SELECT IN statement with mdb or SDE with:

[NAME] IN
(SELECT [NAME]
FROM RoadLinework_FC As Tmp
GROUP BY [NAME]
HAVING Count( [NAME]) > 1)

fgdb has issues with certain SQL functions. While most work, others (including "GROUP BY") don't.
This is really RDBMS dependent (also for syntax).

And when it can be done, biggest trick is to ensure a 1:1 return, not 1:many or many:1.

Or, as said above, "It's fixed in 10"

I tried this in Select by Attributes (from the UI, not GP). I was able to use the subquery as far as group by, but have HAVING bit was rejected.

RayChilcote

In Select by Attributes, can do a SELECT IN statement with mdb or SDE with:

[NAME] IN
(SELECT [NAME]
FROM RoadLinework_FC As Tmp
GROUP BY [NAME]
HAVING Count( [NAME]) > 1)

fgdb has issues with certain SQL functions. While most work, others (including "GROUP BY") don't.
This is really RDBMS dependent (also for syntax).

And when it can be done, biggest trick is to ensure a 1:1 return, not 1:many or many:1.

Or, as said above, "It's fixed in 10"

LanceShipman

Join the Conversation

Signed in members can post, follow updates, and more. New here? Register a free account.

Quick Links

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.