Sqlite3 (Or General SQL) Retrieve Nth Row of a Query Result

SQLite3 (or general SQL) retrieve nth row of a query result

add LIMIT 1 and OFFSET <n> to the query

example SELECT * FROM users LIMIT 1 OFFSET 5132;

SQLite get the 12th record - most efficient?

You want OFFSET.

SELECT mycol FROM mytable ORDER BY mycol LIMIT 1 OFFSET 11;

Shorthand version:

SELECT mycol FROM mytable ORDER BY mycol LIMIT 11,1;

Link to documentation which describes OFFSET as follows:

The optional OFFSET following LIMIT
specifies how many rows to skip at the
beginning of the result set.

How to select the nth row in a SQL database table?

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.

SQL: How to select 1st, 3rd, 11th and nth row from a table?

If there is a primary key defined for the table that is an integer based data type--both MySQL and SQLite have auto_increment for example--then you can use:

SELECT t.*
  FROM TABLE t
 WHERE t.id IN (1,3, 11)

...where id is the auto_increment column.

There's very little detail to go on, but MySQL and SQLite do not have analytic query support, making queries rather complicated:

SELECT y.*
  FROM (SELECT t.*,
                        (SELECT COUNT(*)
                            FROM TABLE x
                           WHERE x.col <= t.col) AS rank
               FROM TABLE t) y
 WHERE y.rank IN (1, 3, 11)

How to select k-th record per field in a single SQL query

While forpas's answer is the better one (Though I think I'd use row_number() instead of rank() here), window functions are fairly recent additions to Sqlite (In 3.25). If you're stuck on an old version and can't upgrade, here's an alternative:

SELECT date, expire_month, expire_year, value
FROM futures AS f
WHERE (date, expire_month, expire_year) =
      (SELECT f2.date, f2.expire_month, f2.expire_year
       FROM futures AS f2
       WHERE f.date = f2.date
       ORDER BY f2.expire_year, f2.expire_month
       LIMIT 1 OFFSET 1)
ORDER BY date;

The OFFSET value is 1 less than the Kth row - so 1 for the second row, 2 for the third row, etc.

It executes a correlated subquery for every row in the table, though, which isn't ideal. Hopefully your composite primary key columns are in the order date, expire_year, expire_month, which will help a lot by eliminating the need for additional sorting in it.

Return row of every n'th record

This is where ROW_NUMBER can help. It requires an order-by clause but this is okay because an order-by is present (and required to guarantee a particular order).

SELECT t.id, t.key
FROM
(
    SELECT id, key, ROW_NUMBER() OVER (ORDER BY key) AS rownum
    FROM datatable
) AS t
WHERE t.rownum % 30 = 0    -- or % 40 etc
ORDER BY t.key

Select one random data that have a common characteristic from a sqlite database

You only call rs.next() once so it will only select the first one from the result set. If the database always retrieves them in the same order (which it will), then you will always get the same one. (And, by the way, * here means all columns, not all rows; the rows you get are determined by the where clause.)

If you don't have a large number of data in the database, you can just select everything into a list and choose a random element of the list:

    ResultSet rs = pst.executeQuery();
    List<String> allQuestions = new ArrayList<>();
    while(rs.next())
    {
        allQuestions.add(rs.getString("Question"));
    }
    if (allQuestions.isEmpty()) {
        System.out.println("No data for this subject");
    } else {
        Random random = new Random();
        question.setText(allQuestions.get(random.nextInt(allQuestions.size())));
    }

If you have a very large number of items in the database, this is however a bad option, as you will load them all into memory. In that case you might want to use some SQL tricks to select a specific row from the database. You can get a count of all rows first using

SELECT COUNT(*) FROM Questions WHERE Subject = ?

Then choose a random number between 1 and the returned value and use the techniques in How to select the nth row in a SQL database table? to get that specific row.

Select the row with maximum/minimum value in SQLite

According to the docs, SQLite allows to select the entire row with max():

SELECT *, max(a) FROM MyTable;

(This is supported since version 3.7.11.)

---

Related Topics