Select single row with same data on other max val

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)

Select single row with same data on other max val

Author

Topic

DMonnik
Starting Member

2 Posts

Posted - 2010-11-11 : 09:06:05

Hi guys...

This is what my data looks like

[tblPerson]
[id] [name] [value]
1 john VAL112
2 ben VAL435
3 john VAL523
4 sam VAL093
5 sam VAL455
6 ben VAL115
7 sam VAL885
8 sam VAL999
9 john VAL432

I need to select the single row for each name with the highest value in the value column using one T-SQL statement so that i get the following result:

2 ben VAL435
3 john VAL523
8 sam VAL999

If anyone can help with the SQL for this id appreciate it.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-11-11 : 09:36:13

select * from
(
select *,row_number() over (partition by [name] order by replace([value],'VAL','')*1 desc as sno from table
) as t
where sno=1

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources