Find row with highest value for a given column

[Anonymous]

I have a set of json files containing some structures that I flatten so to have a row with four fields: field1, field2,field3,field4.

I wanna keep for each field1, the row with the highest field4. This would be equivalent in SQL to write

SELECT * from F fields where NOT EXISTS (SELECT 1 from F fields2 where fields2.field1 = fields.field1 and fields2.field4 > fields.field4)

Obviously I am not using a relational database, so I can't use SQL. Old posts on this forum suggest you can use a tUniqRow after having sorted the rows but this component doesn't exist anymore in Talend Open Studio For Data Integration 6

Thank you for your help

Edmondo

[cterenzi]

You can use a tHSQLDbOutput component to create an in memory database which you could query in the manner you describe.