Finding TOP values without resorting to cursors!
-
This may seem a very simple question, but I am trying to find a way to list say the top 5 values (or less if fewer exist) for each person in a table witout having to resort to cursors.
Here is a simplified version of the problem:
CREATE TABLE tbl1
(
id int IDENTITY NOT NULL PRIMARY KEY,
PersonCode varchar(6) NOT NULL,
DataValue int
)
go
INSERT tbl1 VALUES ('ABC123, 18')
INSERT tbl1 VALUES ('ABC123, 7')
INSERT tbl1 VALUES ('ABC123, 14')
INSERT tbl1 VALUES ('ABC123, 92')
INSERT tbl1 VALUES ('ABC123, 12')
INSERT tbl1 VALUES ('ABC123, 15')
INSERT tbl1 VALUES ('ABC123, 3')
INSERT tbl1 VALUES ('ABC123, 6')
INSERT tbl1 VALUES ('ABC123, 68')
INSERT tbl1 VALUES ('DEF456, 5')
INSERT tbl1 VALUES ('DEF456, 6')
INSERT tbl1 VALUES ('DEF456, 78')
INSERT tbl1 VALUES ('GHI789, 63')
INSERT tbl1 VALUES ('GHI789, 12')
INSERT tbl1 VALUES ('GHI789, 148')
INSERT tbl1 VALUES ('GHI789, 15')
INSERT tbl1 VALUES ('GHI789, 11')
INSERT tbl1 VALUES ('GHI789, 44')
INSERT tbl1 VALUES ('GHI789, 32')
go
Now, the results I want are:
PersonCode DataValue
---------- ---------
ABC123 92
ABC123 68
ABC123 18
ABC123 15
ABC123 14
DEF456 78
DEF456 6
DEF456 5
GHI789 148
GHI789 63
GHI789 44
GHI789 32
GHI789 15
Two of us have been struggling over this for around 3 hrs and we have the added pressure of a very tight deadline!
Hopefully someone can help before Mr Cursor comes a-callin'
-
Based on your simplified example, try this:
SELECT
* FROM tbl1 a
where
a.DataValue in (select top 5 b.DataValue from tbl1 b where b.PersonCode = a.PersonCode order by b.DataValue desc
)
order
by a.PersonCode asc, a.DataValue desc
go
-
Use a temp table to assign a "rank" to records based on whatever sort order you need.
Join this to itself via a derived table, to include only items of a certain rank - in your case the top 5 ranked items within each group of PersonCode.
-- Test Data
CREATE TABLE #tbl1
(
id int IDENTITY NOT NULL PRIMARY KEY,
PersonCode varchar(6) NOT NULL,
DataValue int
)
go
INSERT #tbl1 (PersonCode, DataValue) VALUES ('ABC123', 18)
INSERT #tbl1 (PersonCode, DataValue) VALUES ('ABC123', 7)
INSERT #tbl1 (PersonCode, DataValue) VALUES ('ABC123', 14)
INSERT #tbl1 (PersonCode, DataValue) VALUES ('ABC123', 92)
INSERT #tbl1 (PersonCode, DataValue) VALUES ('ABC123', 12)
INSERT #tbl1 (PersonCode, DataValue) VALUES ('ABC123', 15)
INSERT #tbl1 (PersonCode, DataValue) VALUES ('ABC123', 3)
INSERT #tbl1 (PersonCode, DataValue) VALUES ('ABC123', 6)
INSERT #tbl1 (PersonCode, DataValue) VALUES ('ABC123', 68)
INSERT #tbl1 (PersonCode, DataValue) VALUES ('DEF456', 5)
INSERT #tbl1 (PersonCode, DataValue) VALUES ('DEF456', 6)
INSERT #tbl1 (PersonCode, DataValue) VALUES ('DEF456', 78)
INSERT #tbl1 (PersonCode, DataValue) VALUES ('GHI789', 63)
INSERT #tbl1 (PersonCode, DataValue) VALUES ('GHI789', 12)
INSERT #tbl1 (PersonCode, DataValue) VALUES ('GHI789', 148)
INSERT #tbl1 (PersonCode, DataValue) VALUES ('GHI789', 15)
INSERT #tbl1 (PersonCode, DataValue) VALUES ('GHI789', 11)
INSERT #tbl1 (PersonCode, DataValue) VALUES ('GHI789', 44)
INSERT #tbl1 (PersonCode, DataValue) VALUES ('GHI789', 32)
go
-- Create an empty temp table for ranking results
Select Identity(int, 1, 1) As RankingSequence,
Cast(id as int) As Id,
PersonCode,
Datavalue
Into #Ranked
From #tbl1
Where 0 = 1
-- Populate the table, ordered by PersonCode and the value to be ranked
Insert Into #Ranked
( Id, PersonCode, Datavalue)
Select
Id, PersonCode, DataValue
From #Tbl1
Order By PersonCode, DataValue Desc
-- get the results out, including only topmost 5 per PersonCode
Select r.PersonCode, r.DataValue
From #Ranked As r
Inner Join
(
Select PersonCode, Min(RankingSequence) + 4 As Number5
From #Ranked
Group By PersonCode
) dtTop5
On (r.PersonCode = dtTop5.PersonCode And
r.RankingSequence <= dtTop5.Number5 )
Order By r.PersonCode, r.DataValue Desc
-
If you're using SQL Server 2005 you should look into the ranking functions... something like this:
SELECT t1.*
FROM tbl1 t1
JOIN (SELECT id, RANK() OVER (PARTITION BY PersonCode ORDER BY DataValue DESC) AS rank_value FROM tbl1 ) t2
ON t1.id = t2.id
WHERE t2.rank_value <= 5
ORDER BY t1.PersonCode ASC, t1.DataValue DESC
-
Doh, didn't read the top... 7,2000 forum. Use one of the examples above then.
-
Thankyou all for replying, even Aaron who inadvertently missed the forum subject title!
Lynn...your solution was the one I first tried. However it doesn't guarantee to always bring back a max of five rows per person:
set nocount on
declare @tbl table(
tbl_id int IDENTITY NOT NULL PRIMARY KEY,
PersonCode varchar(6) NOT NULL,
DataValue int)
INSERT @tbl(PersonCode, DataValue) VALUES ('ABC123', 18)
INSERT @tbl(PersonCode, DataValue) VALUES ('ABC123', 7)
INSERT @tbl(PersonCode, DataValue) VALUES ('ABC123', 14)
INSERT @tbl(PersonCode, DataValue) VALUES ('ABC123', 92)
INSERT @tbl(PersonCode, DataValue) VALUES ('ABC123', 12)
INSERT @tbl(PersonCode, DataValue) VALUES ('ABC123', 15)
INSERT @tbl(PersonCode, DataValue) VALUES ('ABC123', 3)
INSERT @tbl(PersonCode, DataValue) VALUES ('ABC123', 6)
INSERT @tbl(PersonCode, DataValue) VALUES ('ABC123', 68)
INSERT @tbl(PersonCode, DataValue) VALUES ('DEF456', 5)
INSERT @tbl(PersonCode, DataValue) VALUES ('DEF456', 6)
INSERT @tbl(PersonCode, DataValue) VALUES ('DEF456', 78)
INSERT @tbl(PersonCode, DataValue) VALUES ('GHI789', 63)
INSERT @tbl(PersonCode, DataValue) VALUES ('GHI789', 15)
INSERT @tbl(PersonCode, DataValue) VALUES ('GHI789', 148)
INSERT @tbl(PersonCode, DataValue) VALUES ('GHI789', 32)
INSERT @tbl(PersonCode, DataValue) VALUES ('GHI789', 44)
INSERT @tbl(PersonCode, DataValue) VALUES ('GHI789', 44)
INSERT @tbl(PersonCode, DataValue) VALUES ('GHI789', 32)
SELECT a.personcode, a.datavalue FROM @tbl a
where a.DataValue in
(select top 5 b.DataValue from @tbl b where b.PersonCode = a.PersonCode order by b.DataValue desc)
order by a.PersonCode asc, a.DataValue desc
Brings back:
personcode datavalue
---------- -----------
ABC123 92.00
ABC123 68.00
ABC123 18.00
ABC123 15.00
ABC123 14.00
DEF456 78.00
DEF456 6.00
DEF456 5.00
GHI789 148.00
GHI789 63.00
GHI789 44.00
GHI789 44.00
GHI789 32.00
GHI789 32.00
PW's solution does work but seemed a little complicated, so I've simplified it and it works a treat! Thankyou.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply