How to Find 11Th Entry in SQL Access Database Table

How can I select the nth row in MS Access?

nested query would do it, something like this:

declare @table table (id int)
insert into @table values (1),(2),(3),(4),(5)

select top 1 id from
(
    select top 3 id from @table
    order by id desc
) t
order by t.id asc

Edit: Just noticed you said it was MS-Access, The select query would still be the same

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.

Problem with returning nth row from MS Access table

The order bys should be reversed:

SELECT TOP 1 
    s.SenseID
FROM
 (SELECT TOP 3 s.SenseID
  FROM Sense AS
  ORDER BY s.SenseID Asc) AS s
ORDER BY 
    s.SenseID Desc;

Get the n'th record for every SerialNumber

MS Access is a lousy database for this sort of thing. You can enumerate the values with a subquery and then use that for the selection:

select t.*
from (select t.*,
             (select count(*)
              from <your table> as t2
              where t2.serialnumber = t.serialnumber and t2.id <= t.id
             ) as seqnum
      from <your table> as t
     ) as t
where seqnum = 2;

The middle subquery is not actually needed. You could put the correlated subquery in the where clause. I find this form clearer in intent.

Selecting Nth Record in an SQL Query

This is a classic interview question.

In Ms SQL 2005+ you can use the ROW_NUMBER() keyword and have the Predicate ROW_NUMBER = n

USE AdventureWorks;
GO
WITH OrderedOrders AS
(
    SELECT SalesOrderID, OrderDate,
    ROW_NUMBER() OVER (ORDER BY OrderDate) AS 'RowNumber'
    FROM Sales.SalesOrderHeader 
)  

SELECT * 
FROM OrderedOrders 
WHERE RowNumber = 5;

In SQL2000 you could do something like

SELECT Top 1 *FROM
[tblApplications]
where [ApplicationID] In
(
    SELECT TOP 5 [ApplicationID]
    FROM [dbo].[tblApplications]
    order by applicationId Desc
)

Return the nth record from MySQL query

SELECT * FROM table ORDER BY ID LIMIT n-1,1

It says return one record starting at record n.

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 Nth Row From A Table In Oracle

Based on the classic answer:

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:127412348064

select * 
  from ( select a.*, rownum rnum
           from ( YOUR_QUERY_GOES_HERE -- including the order by ) a
          where rownum <= N_ROWS )
 where rnum >= N_ROWS
/

SQL Server Select top 10-20 results?

WITH T AS
(
SELECT TOP 20 name, 
       row_number() OVER (ORDER BY id) AS RN
FROM Products
ORDER BY id
)
SELECT 
       MAX(CASE WHEN RN <=10 THEN name END) AS Col1,
       MAX(CASE WHEN RN > 10 THEN name END) AS Col2
FROM T       
GROUP BY RN % 10

---

Related Topics