How to get first/top row of the table in Sqlite via Sql Query

Question

I need to fetch the first/top row of a table in a Sqlite database.

But my program throws an SQLException "Sqlite Syntax Error: Syntax error near '1' " for the query that I am using:

SELECT TOP 1 * 
FROM SAMPLE_TABLE

That I guess is a syntax particularly for MS SQL SERVER and MS ACCESS. Right now I am using.

SELECT *
FROM SAMPLE_TABLE
LIMIT 1

What is the best solution for this problem?

Should be mentioned that developer should not depend on physical order of records in the Table. This is danger idea. — Ruslan Zasukhin, Jun 14 '17 at 08:14
Possible duplicate of [How to get Top 5 records in SqLite?](https://stackoverflow.com/questions/2728999/how-to-get-top-5-records-in-sqlite) — Mehdi Dehghani, May 11 '18 at 14:42

score 177 · Answer 1 · edited Oct 18 '19 at 11:20

177

Use the following query:

SELECT * FROM SAMPLE_TABLE ORDER BY ROWID ASC LIMIT 1

Note: Sqlite's row id references are detailed here.

edited Oct 18 '19 at 11:20

Uwe Keim

39,551
56
175
291

answered Mar 23 '11 at 16:10

Achim

1,791
1
9
4

score 32 · Answer 2 · edited Aug 12 '14 at 09:59

32

LIMIT 1 is what you want. Just keep in mind this returns the first record in the result set regardless of order (unless you specify an order clause in an outer query).

edited Aug 12 '14 at 09:59

zkanoca

9,664
9
50
94

answered Mar 23 '11 at 16:09

Jordan Parmer

36,042
30
97
119

2

`LIMIT 1` executes the full query, and then discards the unneeded results. There are several mailing list discussions about it and `COUNT(*)`. It is different than finding the first matching record with `TOP 1`. Once the first record is found the query can stop and return the result. – jww Mar 24 '19 at 16:34

How to get first/top row of the table in Sqlite via Sql Query

2 Answers2