Not very proficient with SQL. Can anyone please suggest a way to LIMIT the number of integer records permitted in a db which is ordered on size of integer.
Perhaps I can test the number being entered and if higher than the lowest then enter it and delete the lowest or don't enter it if lower.
My real question is whether there is an obvious easy SQL statement to do the database bit.
Thanks for replying. Sorry I wasn't clear.
No I mean limit length of db to, say, 20 records.
The content may mean putting one in and taking one out lower down.
I think I could do it with some tests and delete etc. but I was hoping there was a simpler way.
I display say 20 in the list but don't want the db to keep growing unnecessarily.
The LIMIT parameter as you say will limit the output, which I have, but I have visions of a huge database for only 20 items.
Thank you both for those links which I will now proceed to.
[EDIT]Have been there and both offer interesting solutions, thank you again.
How about something like this. I am sorry I did not have a chance to test it because it is not often that you like to limit the number of records in a table. Always have a back up of the database when you delete. So be cautious.
B4X:
Dim MyRecs
Dim MaxRecs As Int =20
MyRecs=SQL1.ExecQuerySingleResult("SELECT count(*) FROM MyTable")
If MyRecs >= MaxRecs Then
SQL1.ExecNonQuery2("DELETE FROM MyTable WHERE ROWID>= ?", Array As Int (MyRecs)) 'every table has a built in ROWID
End If
SQL1.ExecNonQuery2("INSERT INTO MyTable VALUES (?,?,?)", Array As String("England","London","Manchester United"))