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
Self-Referencing Constraint in Ms SQL
How to Convert "2019-11-02T20:18:00Z" to Timestamp in Hql
How to Substitute a String If Record Is Null in T-Sql
How to Count the Number of Words in a String in Oracle
Insert Deleted Values into a Table Before Delete with a Delete Trigger
Why Does Nvl Always Evaluate 2Nd Parameter
Postgresql - Order by an Array
Ordered Count of Consecutive Repeats/Duplicates
Search If Number Is Contained Within an Expression Like: 1-3,5,10-15,20
Bigquery Update Nested Array Field
SQL Server: Order by Parameters in In Statement
Linq Orderby. Does It Always Return the Same Ordered List
How to Determine the Auto-Generated Primary Key Used as a Foreign Key for Another Table
How to Change the Formatting for My Return Values in This Function
Why Postgres Is Not Using the Index in My Query
Setting Variables in SQL Functions/Probs
Problem with Alter Then Update in Try Catch with Tran Using Transact-Sql