SQL Server Performance Forum – Threads Archive
Query to select top record
Hi I want to know query to select top 5 to 15 records from a table in SQLServer. Thanks in Advance Chandra Prakash Singhselect top 5 * from tablea order by col1, col2 or old style set rowcount 5
select * from tablea order by col1, col2
set rowcount 0 Cheers
Twan
I think you need this Select
Top 15 columns from
(
select top 20 columns from yourTable order by keycol Desc
)T
order by keycol Asc Refer more pagination techniques here
http://www.aspfaq.com/show.asp?id=2120 Madhivanan Failing to plan is Planning to fail
Madhivanan’s solution is correct for the "TOP 5-to-20" in descending order only. Here’s the syntax where you can replace ASC with DESC to find the rows for either ordering method: SELECT TOP 10 T.col
FROM MyTable T
WHERE T.keycol NOT IN
(SELECT TOP 5 T.keycol FROM MyTable T ORDER BY T.keycol ASC)
ORDER BY T.keycol ASC If you have multiple key columns that you need to order by, use the syntax for a correlated subquery (plus you can mix-and-match): SELECT TOP 10 T1.col
FROM MyTable T1
WHERE T1.key1 NOT IN
(SELECT TOP 5 T2.key1 FROM MyTable T2
WHERE T2.key1 = T1.key1 AND T2.key2 = T1.key2
ORDER BY T2.key1 ASC, T2.key2 ASC)
ORDER BY T1.key1 ASC, T1.key2 ASC
]]>