Hi all!
I'm getting crazy about this "simple" problem:
I've got a point table (file geodatabase) with two fields: [fishnet] and [score]
I want to:
1) group points for each [fishnet]
2) sort points DESC basing on [score] field
3) select the first 4 rows for each [fishnet]
4) export in a new table/feature
all in a modelbuilder flow.
this query works perfectly in Postgres (of course without [ ]):
SELECT [fishnet], [score] FROM
(SELECT [fishnet], [score], row_number() over (partition BY [fishnet] ORDER BY [score] DESC) AS partitby
FROM table) tab WHERE tab.partitby < 4
Here is an example of my INPUT table ----> Here is an example of my OUTPUT table
[fishnet] [score] [fishnet] [score]
100 1 100 7
100 5 100 5
100 7 100 2
100 0 100 1
100 1 101 6
100 2 101 0
100 0 ........
101 0
101 6
........
Attached the SQL query in Postgres and the corresponding result:
[ATTACH=CONFIG]22432[/ATTACH]
Solution 1:
Use the above Postgres SQL query in modelbuilder flow: How can I do that?
Solution 2:
Use modelbuilder tools to obtain the same result: How? suggestions?
Thank you very much in advance!
Paolo