Any tools that allow "GROUP BY" or "DISTINCT" in queries on file gdb data

402
4
Jump to solution
07-31-2012 09:24 AM
HarryBowman
New Contributor III
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.
0 Kudos
1 Solution

Accepted Solutions
LanceShipman
Esri Regular Contributor
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).

View solution in original post

0 Kudos
4 Replies
LanceShipman
Esri Regular Contributor
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).
0 Kudos
RayChilcote
Occasional Contributor
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"
0 Kudos
HarryBowman
New Contributor III
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.
0 Kudos
HarryBowman
New Contributor III
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.
0 Kudos