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

PaoloPensalfini — Thu, 07 Mar 2013 12:52:23 GMT

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

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

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