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

sqlsqlite

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?

Use the following query:

SELECT * FROM SAMPLE_TABLE ORDER BY ROWID ASC LIMIT 1

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

There are ways of doing this in optional parts of the standard, but a lot of databases support their own way of doing it.

A really good site that talks about this and other things is http://troels.arvin.dk/db/rdbms/#select-limit.

Basically, PostgreSQL and MySQL supports the non-standard:

SELECT...
LIMIT y OFFSET x

Oracle, DB2 and MSSQL supports the standard windowing functions:

SELECT * FROM (
  SELECT
    ROW_NUMBER() OVER (ORDER BY key ASC) AS rownumber,
    columns
  FROM tablename
) AS foo
WHERE rownumber <= n

(which I just copied from the site linked above since I never use those DBs)

Update: As of PostgreSQL 8.4 the standard windowing functions are supported, so expect the second example to work for PostgreSQL as well.

Update: SQLite added window functions support in version 3.25.0 on 2018-09-15 so both forms also work in SQLite.

Try:

INSERT INTO table1 ( column1 )
SELECT  col1
FROM    table2

This is standard ANSI SQL and should work on any DBMS

It definitely works for: