Geek Answers Handbook

Choose the second largest from the table without limits

How can we select the second largest character or something from the table without using LIMIT ? I know this is possible using LIMIT, but is it possible without using?

Suppose we have id and label columns .

+5
source share
4 answers

Assuming the labels are unique , the following query gives you the second largest character.

SELECT   MAX(marks)
FROM     ATable
WHERE    marks < (SELECT MAX(marks) FROM ATable)

To get the whole record, you can wrap it in INNER JOIN

SELECT  t1.*
FROM    ATable t1
        INNER JOIN (
          SELECT   marks = MAX(marks)
          FROM     ATable
          WHERE    marks < (SELECT MAX(marks) FROM ATable)
        ) t2 ON t2. marks = t1.marks
+8
source 
select max(number), id 
from <tableName>
where number < (select max(number) from <tableName>)
+1
source 

SELECT MAX(marks) FROM TABLE 
WHERE marks NOT IN (SELECT MAX(marks) FROM TABLE)

LIMIT , , , ?

0

If you need a whole row (all columns), it will do this. In addition, it will always return only one row, even if there are several with the same 2nd maximum value.

Select top 1 * 
  From (Select Top 2 * 
          From TABLE 
         Order By marks desc
       ) a
Order By marks asc

If you only need one line with a real 2nd maximum value, you should use:

select Top 1 * 
  from TABLE
 where marks < (select max(marks) from TABLE)
Order by max desc

This can also be done using CTE (SQL Server 2005+):

;With a as 
(
Select Dense_Rank() over (order by marks desc) as nRank,
       *
  From TABLE
)
Select Top 1 * 
  from a
 Where nRank=2

If you want to see all rows with second maximum marks, simply remove TOP 1 from the previous query.

0
source

More articles:


All Articles