How to get N rows starting from row M from sorted table in T-SQL
UPDATE If you you are using SQL 2012 new syntax was added to make this really easy. See Implement paging (skip / take) functionality with this query
I guess the most elegant is to use the ROW_NUMBER function (available from MS SQL Server 2005):
WITH NumberedMyTable AS
(
SELECT
Id,
Value,
ROW_NUMBER() OVER (ORDER BY Id) AS RowNumber
FROM
MyTable
)
SELECT
Id,
Value
FROM
NumberedMyTable
WHERE
RowNumber BETWEEN @From AND @To
Selecting n Number of Rows Starting From The nth Row
In SQL Server 2005/2008, you'll need to use a row number, as Maurice mentioned. The LIMIT syntax unfortunately doesn't work. The query will look like this (assuming you want to order by date)
WITH numbered AS
(
SELECT
Posts.Slug,
Comments.commentId,
Comments.[date],
Comments.name, Comments.[text],
ROW_NUMBER() OVER (ORDER BY date) as rownum
FROM Comments
INNER JOIN Posts ON Comments.postId = Posts.Id
WHERE Comments.approved = 0
)
SELECT * FROM numbered WHERE rownum BETWEEN @startrow AND @endrow
The above assumes @startrow and @endrow are parameters passed into your stored procedure.
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.
Selecting the first N rows of each group ordered by date
As well as the row_number
solution, another option is CROSS APPLY(SELECT TOP
:
SELECT m.masterid,
d.detailid,
m.numbers,
d.date_time,
d.value
FROM masters AS m
CROSS APPLY (
SELECT TOP (3) *
FROM details AS d
WHERE d.date_time >= '2020-01-01'
AND m.masterid = d.masterid
) AS d
WHERE m.tags LIKE '%Tag2%'
ORDER BY m.masterid DESC,
d.date_time;
This may be faster or slower than row_number
, mostly depending on cardinalities (quantity of rows) and indexing.
If indexing is good and it's a small number of rows it will usually be faster. If the inner table needs sorting or you are anyway selecting most rows then use row_number
.
SQL query that can select n rows order by and then return m row
The literal interpretation would lead to
select top 1000 from tbl order by columnname
And the next step to
SELECT TOP 100 FROM (select top 1000 from tbl order by columnname) SQ
But that gives no different than a direct
select top 100 from tbl order by columnname
Unless you are after 2 different orderings
SELECT TOP 100
FROM (
select top 1000 from tbl
order by columnname) SQ
ORDER BY othercolumn
or switching between asc/desc
SELECT TOP 100
FROM (
select top 1000 from tbl
order by columnname ASC) SQ
ORDER BY columnname DESC
T-SQL How to select only Second row from a table?
Assuming SQL Server 2005+ an example of how to get just the second row (which I think you may be asking - and is the reason why top
won't work for you?)
set statistics io on
;with cte as
(
select *
, ROW_NUMBER() over (order by number) as rn
from master.dbo.spt_values
)
select *
from cte
where rn = 2
/* Just to add in what I was running RE: Comments */
;with cte as
(
select top 2 *
, ROW_NUMBER() over (order by number) as rn
from master.dbo.spt_values
)
select *
from cte
where rn = 2
Get top 1 row of each group
;WITH cte AS
(
SELECT *,
ROW_NUMBER() OVER (PARTITION BY DocumentID ORDER BY DateCreated DESC) AS rn
FROM DocumentStatusLogs
)
SELECT *
FROM cte
WHERE rn = 1
If you expect 2 entries per day, then this will arbitrarily pick one. To get both entries for a day, use DENSE_RANK instead
As for normalised or not, it depends if you want to:
- maintain status in 2 places
- preserve status history
- ...
As it stands, you preserve status history. If you want latest status in the parent table too (which is denormalisation) you'd need a trigger to maintain "status" in the parent. or drop this status history table.
SQL Server SELECT LAST N Rows
You can do it by using the ROW NUMBER BY PARTITION Feature also. A great example can be found here:
I am using the Orders table of the Northwind database... Now let us retrieve the Last 5 orders placed by Employee 5:
SELECT ORDERID, CUSTOMERID, OrderDate
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY EmployeeID ORDER BY OrderDate DESC) AS OrderedDate,*
FROM Orders
) as ordlist
WHERE ordlist.EmployeeID = 5
AND ordlist.OrderedDate <= 5
Manually specify starting value for Row_Number()
Just add the value to the result of row_number()
:
select 3258170 - 1 + row_number() over (order by (select NULL)) as idd
The order by
clause of row_number()
is specifying what column is used for the order by. By specifying a constant there, you are simply saying "everything has the same value for ordering purposes". It has nothing, nothing at all to do with the first value chosen.
To avoid confusion, I replaced the constant value with NULL. In SQL Server, I have observed that this assigns a sequential number without actually sorting the rows -- an observed performance advantage, but not one that I've seen documented, so we can't depend on it.
T-SQL Query: Select only the most recent row (for each computer name)
You can do this by using RowNumber which gives a row number to every row. Since you want to give row number starting from 1 for each computerName based on TimeStamp, we need to partition it by Computername
select * from (
select *, ROW_NUMBER() over (partition by ComputerName order by Timestamp desc) as rno
from ComputerStatus
) as T where rno = 1
MSDN
Related Topics
Regular Expression to Match Common SQL Syntax
Postgresql Order by Issue - Natural Sort
Delete Parent If It's Not Referenced by Any Other Child
SQL Update Order of Evaluation
What Is the Equivalent Postgresql Syntax to Oracle's Connect by ... Start With
Oracle: Combine Multiple Results in a Subquery into a Single Comma-Separated Value
How to Make a Parametrized SQL Query on Classic Asp
Selecting the Second Row of a Table Using Rownum
How to Select Even Records from a Table in Oracle
Hive Query Performance for High Cardinality Field
Does the Jdbc Spec Prevent '' from Being Used as an Operator (Outside of Quotes)
Call Dynamic SQL from Function
Why Do Null Values Come First When Ordering Desc in a Postgresql Query
Return Multiple Columns and Rows from a Function Postgresql Instead of Record