Program to find the second largest odd number in SQL Server

Rajnilari2015
Posted by Rajnilari2015 under Sql Server category on | Points: 40 | Views : 104
Let's say we have a table of integers as under

number
--------
78
92
44
63
71
97


The objective is to find the second largest odd number. Below TSQL Script will do so

Declare @t table(number int)
Insert into @t values(78),(92),(44),(63),(71),(97)


SELECT OddNums
FROM
(SELECT
Rn=ROW_NUMBER() OVER(ORDER BY X.OddNums DESC)
,X.*
FROM(
SELECT
OddNums = CASE WHEN number % 2 <> 0 THEN number END
FROM @t)X
WHERE X.OddNums IS NOT NULL)Y
WHERE Y.Rn = 2

/*Result
-------

OddNums
71

*/

Comments or Responses

Login to post response