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
.
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
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
How to select the first N rows of each group?
You can do the counting using a correlated subquery:
SELECT b.BookId, a.AuthorId, a.AuthorName, b.Title
FROM Author a join
Book b
on a.AuthorId = b.AuthorId
where (select count(*)
from book b2
where b2.bookId <= b.BookId and b2.AuthorId = b.AuthorId
) <= 2;
For a small database this should be fine. If you create a composite index on Book(AuthorId, BookId)
then that will help the query.
How to skip the first n rows in sql query
Query: in sql-server
DECLARE @N INT = 5 --Any random number
SELECT * FROM (
SELECT ROW_NUMBER() OVER(ORDER BY ID) AS RoNum
, ID --Add any fields needed here (or replace ID by *)
FROM TABLE_NAME
) AS tbl
WHERE @N < RoNum
ORDER BY tbl.ID
This will give rows of Table, where rownumber is starting from @N + 1
.
Select first N rows of all non-empty SQL Server tables
SQL Server has the unsupported sp_MSforeachtable
. So, you can do:
exec sp_MSforeachtable @command1='select top (3) * from ? where exists (select 1 from ?)';
Otherwise, you would be using a cursor or other looping mechanism to achieve basically the same thing.
EDIT:
Jeroen makes a very good suggestion:
exec sp_MSforeachtable @command1='if exists (select 1 from ?) select top (3) ''?'' as table_name, t.* from ? t';
This also adds the table name for extra spice.
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
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.
Related Topics
Ora-00936: Missing Expression Oracle
Ssrs Remove Column from Report
Sql:Casting a String to Ids with in Clause
Trouble Making a Running Sum in Access Query
Natural Sort Supporting Big Numbers
Sql: Finding the Closest Lat/Lon Record on Google Bigquery
Handling Unicode Characters That Aren't Displayed Correctly in SQL Query
T-Sql: How to Join @Variable Tables (Another Try)
Delete Records Which Are Considered Duplicates Based on Same Value on a Column and Keep the Newest
How to Display Number Value in Words
How to Import an Excel Spreadsheet into SQL Server 2008R2 Database