Jump to content

[TOPIC: topicViewTemplate]
[GLOBAL: userSmallPhoto]
Photo

ROW_NUMBER(() in SQlite3 and Lua
Started by J@V0 Aug 12 2019 01:54 AM

- - - - -
8 replies to this topic
row_number(); sqlite3 database

Best Answer richard11 , 12 August 2019 - 02:31 PM

Strange. SQLite must always return the last record in the group then, in which case your DESC ordering should be safe.

I've thought of another, potentially faster method though. If you stick a HAVING on the end of the group, you can filter the records within that group. Filtering by the minimum id should therefore return groups of just one record, like this:

SELECT `id`,`buildingOnMapId`,`x`,`y` FROM `builtBuildings` GROUP BY `buildingOnMapId` HAVING MIN(`id`);

Not needing an order by or an inner select should be faster, but I've no idea what the performance of HAVING() is like. Regardless, if this one works it's probably more elegant.

[TOPIC CONTROLS]
[/TOPIC CONTROLS]
[modOptionsDropdown]
[/modOptionsDropdown]
[reputationFilter]
[TOPIC: post.html]
#1

J@V0

[GLOBAL: userInfoPane.html]
J@V0
  • Enthusiast

  • 70 posts
  • Corona SDK

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).

 

2019-08-12_11h48_14.png

 

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...



[TOPIC: post.html]
#2

nick_sherman

[GLOBAL: userInfoPane.html]
nick_sherman
  • Corona Geek

  • 1,810 posts
  • Corona SDK

Seems like row_number() is a pretty new addition to SQLite (Sept 2018), so I think your only option is to iterate over the rows in Lua, sorted as above, and manually add the required rows to a new table.



[TOPIC: post.html]
#3

richard11

[GLOBAL: userInfoPane.html]
richard11
  • Contributor

  • 448 posts
  • Corona SDK

Are you just trying to return your 'id' columns, one for each unique 'buildingOnMapId' value? If so, this should work...

for row in globalData.db:nrows( [[SELECT `id`,`buildingOnMapId`,`buildingType` FROM `builtBuildings` GROUP BY `buildingOnMapId` ORDER BY `id` ASC;]] ) do
	print("id = " .. row.id .. " , buildingOnMapId = " .. row.buildingOnMapId .. " , buildingType = " .. row.buildingType)
end

The GROUP BY clause groups all records where the defined field matches, so that only one of those records is returned. Combined with your ORDER BY this should always be the record with the lowest ID.

 

As a side note, it's worth getting into the habit of prefixing your field and table names with something to ensure that they're not interpreted as SQL commands. I for example, prefix db for databases, tbl for tables, and fld for fields. That way, something like `id` becomes `fldId` and there's no risk of it being interpreted as a statement in database engines that interpret 'id' if you forget to escape it.



[TOPIC: post.html]
#4

J@V0

[GLOBAL: userInfoPane.html]
J@V0
  • Enthusiast

  • 70 posts
  • Corona SDK

Sorry my post wasn't complete, I need the x and y coordinates of the first row of each unique 'buildingOnMapId'. My current query is the same as the one Richard mentioned:

"SELECT buildingType, x, y FROM builtBuildings GROUP BY buildingOnMapId ORDER BY id ASC"

But that returns the last row of each unique 'buildingOnMapId'. I.e. id 7 and 12 in the example of my previous spot.

 

 

I though of iterating through the rows and only getting the first entry but would have been nice to manage it directly with a simple query. I will end up doing that if we can't find anything better.

 

Thanks for the tip on the prefix, I will do that next time I cleanup my code.



[TOPIC: post.html]
#5

richard11

[GLOBAL: userInfoPane.html]
richard11
  • Contributor

  • 448 posts
  • Corona SDK

It sounds like SQLite performs the group by before the order by then. In which case you'll need to use an inner select to return an ordered list before grouping, like this:

SELECT * FROM (SELECT `id`,`buildingOnMapId`,`x`,`y` FROM `builtBuildings` ORDER BY `id` ASC) tmp GROUP BY `buildingOnMapId`

If SQLite is like MariaDB, it's then also possible that it ignores the order by clause of inner selects for performance. In which case with MariaDB you can work around that by adding a limit clause, so the same might work with SQLite:

SELECT * FROM (SELECT `id`,`buildingOnMapId`,`x`,`y` FROM `builtBuildings` ORDER BY `id` ASC LIMIT 100000) tmp GROUP BY `buildingOnMapId`

Just make sure the limit is a number higher than anything you're likely to actually return, so that it's not actually a limit.

Sorry, I'm on my mobile so can't format the above properly, or test, and I'm not overly experienced with SQLite specifically - I'm just coming at this from a generic SQL perspective. Hope it works!

[TOPIC: post.html]
#6

J@V0

[GLOBAL: userInfoPane.html]
J@V0
  • Enthusiast

  • 70 posts
  • Corona SDK

Thanks Richard. I tried your suggestion (both with and without the limit) but I get the same result (the last row is returned instead of the first one).

 

I experimented by changing mine and your query from ORDER BY `id` ASC to ORDER BY `id` DESC and I now get the first row. I guess that it is the last query that is kept in memory/returned so in that case it is the lowest id.

 

Just wondering if that is a safe way of doing it?



[TOPIC: post.html]
#7

richard11

[GLOBAL: userInfoPane.html]
richard11
  • Contributor

  • 448 posts
  • Corona SDK

  Best Answer

Strange. SQLite must always return the last record in the group then, in which case your DESC ordering should be safe.

I've thought of another, potentially faster method though. If you stick a HAVING on the end of the group, you can filter the records within that group. Filtering by the minimum id should therefore return groups of just one record, like this:

SELECT `id`,`buildingOnMapId`,`x`,`y` FROM `builtBuildings` GROUP BY `buildingOnMapId` HAVING MIN(`id`);

Not needing an order by or an inner select should be faster, but I've no idea what the performance of HAVING() is like. Regardless, if this one works it's probably more elegant.

[TOPIC: post.html]
#8

J@V0

[GLOBAL: userInfoPane.html]
J@V0
  • Enthusiast

  • 70 posts
  • Corona SDK

Well as usual Richard has the solution :)

Works perfectly with the HAVING!

 

I will check the performance but from my limited testing it looks okay so far.



[TOPIC: post.html]
#9

richard11

[GLOBAL: userInfoPane.html]
richard11
  • Contributor

  • 448 posts
  • Corona SDK

Glad to hear.

Setting up indexes on the buildingOnMapId and id fields should help with performance regardless, at the cost of using up more storage. 99% of the time, the speed increase of indexes far outweighs the expense of storing them, so as a general rule of thumb you should create an index for any field that you use in a join, group by, or where/having clause.


[topic_controls]
[/topic_controls]

Also tagged with one or more of these keywords: row_number();, sqlite3, database