如何在不使用top的情况下从表中获取前2条记录 [英] how to get top 2 record from the table without using top

问题描述

大家好，

我曾在面试中问一个qns，如果我有一个包含3个字段和3条记录的表.

S_No Student_Name Student_marks
1 A 90
2 B 78
3 C 95

现在，我想使用TOP函数选择前2个标记的学生姓名.

Hello all,

I had asked a qns in Interview that If i Have a table with 3 fields and 3 records.

S_No Student_Name Student_marks
1 A 90
2 B 78
3 C 95

Now i want to select student name with Top 2 marks with out using TOP function.

推荐答案

select s_no,Student_name,Student_marks from
(
select Row_number() over(order by Student_marks desc) as a,
s_no,Student_name,Student_marks from tbl
)as temptbl
where a<3

快乐编码！
:)

HAppy Coding!
:)

这里有几个选项.正如vivektiwari97701指出的那样，一种方法是使用ROWCOUNT.您可以指定ROWCOUNT为2并按Student_marks的降序排列，如下所示:

There are a couple options here. One is to use ROWCOUNT, as vivektiwari97701 pointed out. You can specify a ROWCOUNT of 2 and order by Student_marks in descending order like so:

SET ROWCOUNT 2

SELECT *
FROM tableName
ORDER BY Student_marks DESC

不要忘了以后关闭ROWCOUNT，因为它适用于所有查询.

另一种可能性是在CTE中使用ROW_NUMBER()函数，如下所示:

Don''t forget to turn ROWCOUNT off afterwards, since it applies to all queries.

Another possibility would be to use the ROW_NUMBER() function in a CTE along like so:

With cteName AS
( SELECT S_No, Student_Name, Student_marks,
ROW_NUMBER() OVER (order by Student_marks DESC) as RowNumber 
FROM tableName)
select *
from cteName
Where RowNumber Between 1 and 3

这样做的好处是它仅适用于此查询，并且如果您设置了数字参数，则可以更改它.这将使您轻松进行分页.另请注意，它介于1和3之间，其中将包括1但不包括3(因此仅记录1和2).

The benefit here is that it only applies to this query and it can be changed if you made the numbers parameters. That would allow you to do paging easily. Also note that it is between 1 and 3, which will include 1 but not include 3 (thus records 1 and 2 only).

我认为以下代码可以为您提供帮助. >

I think following code can help you..

SELECT SNO,STUDENTNAME,MARKS FROM (
         SELECT SNO,STUDENTNAME,MARKS ,ROW_NUMBER()
         OVER(ORDER BY MARKS DESC) ROWNUM 
         FROM STUDENTMARKS
) As A WHERE A.ROWNUM < 3

谢谢

Thank you

这篇关于如何在不使用top的情况下从表中获取前2条记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！