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
Select next 20 rows after top 10
You need to use something like a CTE (Common Table Expression) and a ROW_NUMBER
to define row numberings for your data set - then select from that numbered CTE for the set of rows you want:
;WITH PagingCte AS
(
SELECT
(list of columns),
RowNum = ROW_NUMBER() OVER (ORDER BY -some-column-of-yours-)
FROM
dbo.memberform
)
SELECT
(list of columns)
FROM
PagingCte
WHERE
RowNum BETWEEN 10 AND 29
In the inner ROW_NUMBER()
window function, you need to define an ordering which defines how the rows are numbered (order by date, or by ID, or whatever makes sense for you).
Without an explicit ordering, there is no next 20 after the first 10 to be had..
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
LIMIT 10..20 in SQL Server
The LIMIT
clause is not part of standard SQL. It's supported as a vendor extension to SQL by MySQL, PostgreSQL, and SQLite.
Other brands of database may have similar features (e.g. TOP
in Microsoft SQL Server), but these don't always work identically.
It's hard to use TOP
in Microsoft SQL Server to mimic the LIMIT
clause. There are cases where it just doesn't work.
The solution you showed, using ROW_NUMBER()
is available in Microsoft SQL Server 2005 and later. This is the best solution (for now) that works solely as part of the query.
Another solution is to use TOP
to fetch the first count + offset rows, and then use the API to seek past the first offset rows.
See also:
- "Emulate MySQL LIMIT clause in Microsoft SQL Server 2000"
- "Paging of Large Resultsets in ASP.NET"
SELECT TOP N with UNION and ORDER BY
A Union query works thus: execute the queries, then apply the order by clause. So with
SELECT TOP 5 [ID], [Description], [Inactive]
FROM #T1
UNION ALL
SELECT TOP 5 [ID], [Description], [Inactive]
FROM #T2
ORDER BY [Inactive], [Description];
you select five arbitrarily chosen records from #T1 plus five arbitrarily chosen records from #T2 and then you order these. So you need subqueries or with clauses. E.g.:
SELECT * FROM
(
(
SELECT TOP 5 [ID], [Description], [Inactive]
FROM #T1
ORDER BY [Inactive], [Description]
)
UNION ALL
(
SELECT TOP 5 [ID], [Description], [Inactive]
FROM #T2
ORDER BY [Inactive], [Description]
)
) t;
So your workaround is not a workaround at all, but the proper query.
How to select top 10% of values in SQL
Assuming you want the first 10% of rows ordered by Value (desc), you can achieve that by using window functions:
select * from (
select ID, Value, COUNT(*) over (partition by ID) as countrows, ROW_NUMBER() over (partition by ID order by Value desc) as rowno from mytable) as innertab
where rowno <= floor(countrows*0.1+0.9)
order by ID, rowno
The floor-thing brings 1 row per 1-10 rows, 2 rows for 11-20 rows and so on.
How to SELECT the last 10 rows of an SQL table which has no ID field?
SQL tables have no implicit ordering, the order has to come from the data.
Perhaps you should add a field to your table (e.g. an int counter) and re-import the data.
However that will only give the order of the import and not the data. If your data has no ordering you have to find out how to add it.
EDIT: you say
...to make sure it imported everything.
What's wrong with using row count?
SQL query to get the top n scores out of a list
Untested, but should work:
select * from users where points in
(select distinct top 3 points from users order by points desc)
Related Topics
How to Set a Column Value to Null in SQL Server Management Studio
Dynamic SQL Column Value Duplicate and Difference Detection Merge Query
In Ms SQL Server, How to "Atomically" Increment a Column Being Used as a Counter
How to Emulate Tagged Union in a Database
Stored Procedure with Variable Number of Parameters
Storing Matrices in a Relational Database
How to Create Delphi 4 Structure to Map Column Names in Xls to Column Names in SQL
How to Perform a Replace on Varbinary Data in SQL
Sqllite Strftime Not Reading Column Value
How to Model Custom Attributes of Entities
Selecting Distinct Combinations
What Is the Easiest Way to Update an Image Field with the Content of a File