Returning one row with MAX - 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 2000 Forums

SQL Server Development (2000)

Returning one row with MAX

Author

Topic

ElCapitan
Starting Member

28 Posts

Posted - 2009-03-10 : 11:14:52

Hi all,
I would like to return only one row where the MAX is the highest. For example, take this result set below...
ID RuleName Precedence
1 CAN_DELETE 10
2 CAN_DELETE 40
3 CAN_DELETE 20
4 CAN_ADD 10
5 CAN_ADD 50
6 CAN_ADD 20
7 CAN_ADD 30

I would like to get two rows out of this, row 2 and row 5 because they have the highest value in the third column. i.e. I would like to end up with the below result set...

2 CAN_DELETE 40
5 CAN_ADD 50

Any help is greatly appreciated.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-03-10 : 11:27:26

[code]Select t.* from Table t
inner join
(Select RuleName, Max(Precedence)as MaxPrec
from Table
Group by RuleName)z
On t.Precedence = z.Maxprec[/code]

ElCapitan
Starting Member

28 Posts

Posted - 2009-03-10 : 11:34:59

Thanks for your reply. Because the ID column is included in the Group By there will be 7 rows in the result set. I want only two as described. If we remove the ID column, we get something closer but the ID column is still needed.

Any more help is appreciated.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-03-10 : 11:37:58

quote:
Originally posted by ElCapitan

Thanks for your reply. Because the ID column is included in the Group By there will be 7 rows in the result set. I want only two as described. If we remove the ID column, we get something closer but the ID column is still needed.

Any more help is appreciated.

Did it work or not?

ElCapitan
Starting Member

28 Posts

Posted - 2009-03-10 : 11:39:11

wait, your query changed. Let me try again.

ElCapitan
Starting Member

28 Posts

Posted - 2009-03-10 : 11:49:02

Yep. This works. I figured there would need to be an inner select. I was wondering if there was another way. Thanks for your time.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-03-10 : 12:02:14

Sodeep..I'm not sure about this..so just checking with you....

Since you are JOINING both the tables...will this work if the MAX(Precedence) say for CAN_ADD is also a value for CAN_DELETE.

for ex: wil it work for the data below.

declare @t table (Id int, col2 char(10),val int)
insert @t
select 1,'add',50 union all
select 2,'add',60 union all
select 3,'add',10 union all
select 4,'delete',30 union all
select 5,'delete',50

Select t.* from @t t
INNER join
(Select col2, Max(val)as MaxPrec
from @t
Group by col2)z
On t.val = z.MaxPrec

I tried..it gave me 3 rows...how do we solve this...just curious.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-03-10 : 12:09:02

Sorry it should be:

Select t.* from Table t
inner join
(Select RuleName, Max(Precedence)as MaxPrec
from Table
Group by RuleName)z
On t.Precedence = z.Maxprec
and t.RuleName = Z.RuleName

ElCapitan
Starting Member

28 Posts

Posted - 2009-03-10 : 12:34:25

I wonder how the below solution performs against your solution sodeep...

Select * from
(
select row_number() over(partition by RuleName order by Precedence DESC) as row_number, [ID] from tblTest
) T
where T.row_number=1

This would though be SQL 2005 onwards. Does anyone know how to do a performance test on this against the other solution?

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-03-10 : 12:38:00

quote:
Originally posted by ElCapitan

I wonder how the below solution performs against your solution sodeep...

Select ID,RuleName,Precendence from
(
select row_number() over(partition by RuleName order by Precedence DESC) as row_number, * from tblTest
) T
where T.row_number=1
This would though be SQL 2005 onwards.

Does anyone know how to do a performance test on this against the other solution?

Yes ,it will work if you are using SQL 2005. Which one ran faster? Check the execution.I think both should be same considering indexes are same.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources