paopen69

SQL Pg query in modelbuilder: group by field1 and select first 4rows basing on field2

Discussion created by paopen69 on Mar 7, 2013
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

Attachments

Outcomes