how to select first N rows from a table in T-SQL?
select top(@count) * from users
If @count
is a constant, you can drop the parentheses:
select top 42 * from users
(the latter works on SQL Server 2000 too, while the former requires at least 2005)
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
.
Selecting N rows in SQL Server
As commented earlier, it's because you reached the number of rows of sys.columns
. Here is another way to generate list of numbers or what others call Numbers Table
or Tally Table
.
This uses cascaded CTE
s and is said to be the fastest way to create a Tally Table:
DECLARE @Range AS INT = 7374
;WITH E1(N) AS( -- 10 ^ 1 = 10 rows
SELECT 1 FROM(VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))t(N)
),
E2(N) AS(SELECT 1 FROM E1 a CROSS JOIN E1 b), -- 10 ^ 2 = 100 rows
E4(N) AS(SELECT 1 FROM E2 a CROSS JOIN E2 b), -- 10 ^ 4 = 10,000 rows
E8(N) AS(SELECT 1 FROM E4 a CROSS JOIN E4 b), -- 10 ^ 8 = 10,000,000 rows
CteTally(N) AS(
SELECT TOP(@Range) ROW_NUMBER() OVER(ORDER BY(SELECT NULL))
FROM E8
)
SELECT * FROM CteTally
You could easily add another CTE if you need more than 10,000 rows.
For more information about Tally Table, read this excellent article by Jeff Moden.
For performance comparisons among ways to generate Tally Tables, read this.
Explanation taken from Jeff's article:
The CTE called
E1
(as in 10E1 for scientific notation) is nothing
more than tenSELECT 1
's returned as a single result set.
E2
does aCROSS JOIN
ofE1
with itself. That returns a single
result set of 10*10 or up to 100 rows. I say "up to" because if the
TOP function is 100 or less, the CTE's are "smart" enough to know that
it doesn't actually need to go any further andE4
andE8
won't
even come into play. If theTOP
has a value of less than 100, not
all 100 rows thatE2
is capable of making will be made. It'll always
make just enough according to theTOP
function.You can follow from there.
E4
is aCROSS JOIN
ofE2
and will
make up to 100*100 or 10,000 rows andE8
is aCROSS JOIN
ofE4
which will make more rows than most people will ever need. If you do
need more, then just add anE16
as aCROSS JOIN
ofE8
and change
the finalFROM
clause toFROM E16
.What's really amazing about this bad-boy is that is produces ZERO
READS. Absolutely none, nada, nil.
SQL - Select first 10 rows only?
In SQL server, use:
select top 10 ...
e.g.
select top 100 * from myTable
select top 100 colA, colB from myTable
In MySQL, use:
select ... order by num desc limit 10
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
MS SQL how to select n rows from table even if table has n - x rows
-- to create a sample table.
CREATE TABLE table1 (ID BIGINT, NAME VARCHAR(255))
INSERT INTO table1 VALUES (1, 'John'), (2, 'Alice')
-- set number of rows you need
DECLARE @RowsReqd INT = 5
-- find out the max ID we want to generate
DECLARE @Limit INT
SELECT @Limit = MAX(ID) FROM table1
-- generate the list
;WITH NumbersList
AS (
SELECT 1 AS Number, 1 AS ID
UNION ALL
SELECT Number + 1, Number % @Limit + 1 FROM NumbersList
WHERE Number < @RowsReqd
)
SELECT T.*
FROM NumbersList NL
INNER JOIN table1 T ON T.ID = NL.ID
ORDER BY NL.Number
OPTION (MAXRECURSION 10000) -- increase this value to generate more numbers
OUTPUT:
ID NAME
1 John
2 Alice
1 John
2 Alice
1 John
Select N rows in aggregate functions SQL Server
Do you have to use the windowing function?
SELECT ID
, CHANNEL
, VENDOR
, NULL_SALES_SET = SUM(CASE WHEN SALES IS NULL THEN 1 ELSE 0 END)
FROM Table
WHERE num_PERIOD <= 111
GROUP BY ID, CHANNEL, VENDOR
Or are you looking for the first 111 num_PERIOD
values allowing for gaps in the num_PERIOD
column?
SELECT t.ID
, t.CHANNEL
, t.VENDOR
, NULL_SALES_SET = SUM(CASE WHEN t.SALES IS NULL THEN 1 ELSE 0 END)
FROM Table t
INNER JOIN ( SELECT i.ID
, i.CHANNEL
, i.VENDOR
, i.num_PERIOD
, rowNum = ROW_NUMBER(PARTITION BY i.ID, i.CHANNEL, i.VENDOR ORDER BY i.num_PERIOD)
FROM Table i ) l
ON t.ID = l.ID
AND t.CHANNEL = l.CHANNEL
AND t.VENDOR = l.VENDOR
AND t.num_PERIOD = l.num_PERIOD
WHERE l.rowNum <= 111
GROUP BY ID, CHANNEL, VENDOR
Edit: Not sure how I overlooked it, but it is necessary to JOIN on the num_PERIOD column.
Edit: Add the number of distinct num_PERIOD
per ID, Channel, Vendor without affecting the NULL_SALES_SET
SELECT t.ID
, t.CHANNEL
, t.VENDOR
-- Counts the NULL Sales when the num_PERIOD is in the
-- first 111 num_PERIODs
, NULL_SALES_SET = SUM(CASE WHEN l.rowNum IS NOT NULL AND t.SALES IS NULL
THEN 1
ELSE 0 END)
-- Counts the distinct num_PERIOD values
, PERIOD_COUNT = COUNT(DISTINCT t.num_PERIOD)
FROM Table t
LEFT OUTER JOIN ( SELECT i.ID
, i.CHANNEL
, i.VENDOR
, i.num_PERIOD
, rowNum = ROW_NUMBER(PARTITION BY i.ID,
i.CHANNEL,
i.VENDOR
ORDER BY i.num_PERIOD)
FROM Table i ) l
ON t.ID = l.ID
AND t.CHANNEL = l.CHANNEL
AND t.VENDOR = l.VENDOR
AND t.num_PERIOD = l.num_PERIOD
AND l.rowNum <= 111
GROUP BY ID, CHANNEL, VENDOR
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.
Related Topics
How to Create a Temporary Function in Postgresql
How to Set Auto Increment Primary Key in Postgresql
SQL Error: Ora-01861: Literal Does Not Match Format String 01861
How to Interpret Precision and Scale of a Number in a Database
SQL Server Replace, Remove All After Certain Character
How to Version Your Database Schema
How to Have an Indexed View in MySQL
Generate All Combinations in SQL
How to Search Multiple Columns in MySQL
List Columns with Indexes in Postgresql
Calculate Business Hours Between Two Dates
Select Data from Date Range Between Two Dates
Creating Temporary Tables in SQL
How to Query for All Dates Greater Than a Certain Date in SQL Server