Limit SQL records stored

[enonod]

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.

 

[gregmartin64]

Do you mean limit an entry to the lowest value already held in an integer field in your database?

 

[enonod]

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.

 

[grant1842]

I just read your last post.

Here's some good reading:
http://stackoverflow.com/questions/3155053/can-we-limit-the-number-of-rows-in-a-table-in-mysql

 

[gregmartin64]

Well you can limit your list to 20 by using the following query

SELECT * FROM Table_Name LIMIT 20

With whatever ordering you require, though the database underneath would still have more records of course.

But there is a great piece on this very subject here :

database - Limit an sqlite Table's Maximum Number of Rows - Stack Overflow

Someone who is asking pretty much the same question.

 

[enonod]

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.

 

[Mahares]

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.

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

 

[enonod]

I'll certainly give that a try (after a sleep) and thanks for the continued assistance.