Tuesday, 30 July 2013

How to get top " n " number of rows from table


Suppose you want top n values from table , so execute following query
with replacement of following item as mention below :

1 )  Replace your our tablename with [table_name]

2 )  Replace your "n" with row numbers  (how many rows you want )
[
Note :  You can do multiply and division operations also
]

Query for Top rows :

SELECT
MainSQL.* FROM ( SELECT t.*,ROW_NUMBER() OVER ( ORDER BY 1 ) Row_Number,COUNT(1) OVER ( ORDER BY 1 RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) Row_Count
FROM (SELECT * FROM [table_name]) t ORDER BY 1 ) MainSQL WHERE Row_Number >= 1
AND Row_Number < ( 1 + 2 ) ORDER BY Row_Number;


Query for Bottom  rows :

SELECT MainSQL.* FROM ( SELECT t.*,ROW_NUMBER() OVER ( ORDER BY 1 ) Row_Number,
COUNT(1) OVER ( ORDER BY 1 RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) Row_Count
FROM (SELECT * FROM [table_name]) t ORDER BY 1 ) MainSQL WHERE Row_Number >= 1
AND Row_Number < ( 10 - 2 ) ORDER BY Row_Number;

Example :

SELECT *
FROM
(SELECT row_number() over (order by transID) AS line_no, *
FROM [dbo].[UserMast]) as users
WHERE
users.transID < 10
OR
users.transID BETWEEN 34 and 67
 

No comments:

Post a Comment