Help with Select best two rows

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 2005 Forums

Transact-SQL (2005)

Help with Select best two rows

Author

Topic

loppis99
Starting Member

6 Posts

Posted - 2007-09-20 : 07:31:16

Hi,
I'm trying to select the two best scores for every item in a table, but i need help with the SQL query.
The table is like this
Item Score
AA 100
AA 110
AA 130
BB 100
BB 120
BB 140

So i want the result like this
Item Score
AA 130
AA 110
BB 140
BB 120

Thanks for any help

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2007-09-20 : 07:51:48

If you're using SQL 2005 you can use the RANK() function

DECLARE @yourtable TABLE (item char(2),Score int)

INSERT INTO @yourtable

SELECT
'AA', 100
UNION all
SELECT
'AA', 110
UNION all
SELECT
'AA', 130
UNION all
SELECT
'BB', 100
UNION all
SELECT
'BB', 120
UNION all
SELECT
'BB', 140

SELECT Item,Score,aRank
FROM
(
select Item,Score,'aRank' = RANK() OVER(PARTITION BY item order by score)
from @yourtable
) a
WHERE aRank <=2

Jim

nr
SQLTeam MVY

12543 Posts

Posted - 2007-09-20 : 07:52:28

select Item, max(Score) from tbl t1 group by Item
union all
select Item, max(score) from tbl t1
where score not in (select max(t2.Score) from tbl t2 where t1.item = t2.item)

==========================================
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.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-20 : 08:48:40

Not tested

select 
	item,score
from
(
	select 
		item,score,
		row_number() over (partition by item order by score desc) as row_num 
	from 
		your_table
) as T
where row_num<=2

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-20 : 08:53:58

Point 2 would work in both 2000 and 2005
http://weblogs.sqlteam.com/mladenp/archive/2005/08/01/7421.aspx

Madhivanan

Failing to plan is Planning to fail

loppis99
Starting Member

6 Posts

Posted - 2007-09-20 : 09:29:46

Thanks,
using SQL 2005 and all examples works, but it says, that the OVER statment is not supported, but the result is ok

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-20 : 09:32:11

quote:
Originally posted by loppis99

Thanks,
using SQL 2005 and all examples works, but it says, that the OVER statment is not supported, but the result is ok

Did you run it in SQL Server 2005?

Madhivanan

Failing to plan is Planning to fail

loppis99
Starting Member

6 Posts

Posted - 2007-09-20 : 11:03:49

I tested it in a view in SQL 2005

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources