126

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?

Improve this question
2
  • 2
    Should be mentioned that developer should not depend on physical order of records in the Table. This is danger idea. Commented Jun 14, 2017 at 8:14
  • Possible duplicate of How to get Top 5 records in SqLite? Commented May 11, 2018 at 14:42

2 Answers 2

Reset to default
189

Use the following query:

SELECT * FROM SAMPLE_TABLE ORDER BY ROWID ASC LIMIT 1

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

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

33

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

Improve this answer

1 Comment

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.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.