Selecting nth highest values - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Selecting nth highest values

Author

Topic

JohnMcLaughlin
Starting Member

28 Posts

Posted - 2010-10-29 : 07:39:10

I have a table that looks like this

R1 0
R1 51
R1 100
R2 0
R2 60
R2 99

I want to know how to Select the nth highest value in column 2 while also grouping by column 1. I would eventually like the table to be 3 separate tables with each value from column 1, like this:

R1 0
R2 0

R1 51
R2 60

R1 100
R2 99

Any help would be much appreciated

nr
SQLTeam MVY

12543 Posts

Posted - 2010-10-29 : 07:51:59

select col1, col2
from
(
select col1, col2, seq = row_number(partition by col1 order by col2 desc)
from tbl
) a
where seq = 2

something like that will give the second highest for each col1.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-10-29 : 08:14:51

quote:
Originally posted by nr

select col1, col2
from
(
select col1, col2, seq = row_number() over(partition by col1 order by col2 desc)
from tbl
) a
where seq = 2

something like that will give the second highest for each col1.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

No, you're never too old to Yak'n'Roll if you're too young to die.

glendcruz
Yak Posting Veteran

60 Posts

Posted - 2010-10-31 : 23:08:17

Declare @Table TABLE
(
Col1 varchar(10),
Col2 int
)
INSERT INTO @Table
SELECT 'R1', 0 UNION ALL
SELECT 'R1', 51 UNION ALL
SELECT 'R1', 100 UNION ALL
SELECT 'R2', 0 UNION ALL
SELECT 'R2', 60 UNION ALL
SELECT 'R2', 99
----- creating a temporary table and inserts all the data with
-- the rowNumber partitining Col1 and ordered by col2
CREATE TABLE #TempTable
(
Col1 varchar(10),
Col2 int,
RowNumber smallInt
)
INSERT INTO #TempTable
SELECT Col1, Col2, RowNumber = row_number()
OVER(PARTITION BY Col1 ORDER BY Col2 )
FROM @Table
------------------
---Displaying the data as per requirement
SELECT Col1, Col2, RowNumber
FROM
(
SELECT Col1, Col2 , RowNumber , seq = row_number()
over(partition by RowNumber order by col1 )
FROM #TempTable
) tp
where seq <= 2
--------------------------------------------------------
(6 row(s) affected)
(6 row(s) affected)
Col1 Col2 RowNumber
---------- ----------- ---------
R1 0 1
R2 0 1
R1 51 2
R2 60 2
R1 100 3
R2 99 3

(6 row(s) affected)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources