CODEWITHSUNDEEP
sqlsqlite
user12637955
user12637955

Reputation:

How to select the 5 most recent records in a table from a SQLite database

I have a SQLite database table schema called Bans like this.

| Name | Ban Reason |
| Noah | Swearing   |
| Liam | Toxicity   |
| Josh | Cheating   |

Such simple data continues for about another 20 rows. Naturally, the latest entries are at the top. In this case, the entry containing Noah would be the most recent addition to the table.

I want to retrieve the Top 5 most recent results. How can I do this using SQLite? I am vaguely aware that LIMIT should be used, but I cannot get it to work properly. Thanks.

Upvotes: 0

Views: 1347

Answers (2)

VBoka
VBoka

Reputation: 9083

SQLITE:

SELECT * 
FROM your_table 
LIMIT 5;

Here is a DEMO

Depending on what you think when you say latest 5 you can add 

ORDER BY your_column DESC

or

ORDER BY your_column ASC

between FROM clause and LIMIT keyword.

But as I see your query uses a little bit different logic and select all that is not in top 5 so I have simulated that like this in SQLite:

SELECT * 
FROM your_table 
where id not in 
(select id from your_table
LIMIT (SELECT COUNT(*) - 5 FROM your_table));

Here is a DEMO for that EXAMPLE.

Upvotes: 2

forpas
forpas

Reputation: 164099

If you want the rows with the top 5 rowids you can do it with an ORDER BY clause and LIMIT:

SELECT * 
FROM tablename
ORDER BY rowid DESC
LIMIT 5

but the correct way to define latest is by using a column like a created_date.

Upvotes: 0

Related Questions