Any tools that allow "GROUP BY" or "DISTINCT" 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.

[LanceShipman · Accepted solution]

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).

[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"

[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.

[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.