I'm trying to get the first result of each group in a Sqlite3 table. In the snapshot below, only hightlighted rows should be returned (first buildingOnMapId of each group).
After some research I believe i need to use row_number and partition_by as explained here, unfortunately that doesn't seem to work in my case. My query is as follows:
for row in globalData.db:nrows("SELECT ROW_NUMBER() OVER ( PARTITION BY buildingOnMapId ORDER BY id ASC) RowNum, buildingType, x, y FROM builtBuildings") do
Note: I planned to limit the results with WHERE RowNum = 1 but I wanted to start with the simplest query...
But the console returns an error:
ERROR: Runtime error near "(": syntax error stack traceback: [C]: in function 'nrows'
I can't see where my syntax is wrong, maybe row_number is not available in sqlite for Lua?
I'm open to any other alternative if there is a better way to achieve this...