Select Highes Value in Group - 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

General SQL Server Forums

New to SQL Server Programming

Select Highes Value in Group

Author

Topic

olibara
Yak Posting Veteran

94 Posts

Posted - 2012-03-08 : 07:19:50

Hello

In the table below I need to select all rows (in red)
having the Highest Value for the Occurs in the Occurs field for each SerialPrefix group

How can I do that ?
Thank for your help

CREATE TABLE TAC(
[pkId] [int] IDENTITY(1,1) NOT NULL,
[SerialPrefix] [nvarchar](8) NULL,
[Occurs] [int] NULL,
[AmbiguityCount] [int] NULL,
[MediaDefID] [uniqueidentifier] NOT NULL,
)

pkId SerialPrefix Occurs AmbiguityCount MediaDefID
397 01193300 1 3 C6094516-9A92-455C-B267-97C9044AA70C
396 01193300 21 3 BAAFFDB3-6707-480F-B7F3-67B1C9631AF4
395 01193300 120 3 3929F49C-54BE-41CB-918C-147A1775AFE1
400 01193400 1 4 C6094516-9A92-455C-B267-97C9044AA70C
401 01193400 1 4 8BAF0901-909A-4E2F-842E-A1503ED4AFE7
399 01193400 18 4 BAAFFDB3-6707-480F-B7F3-67B1C9631AF4
398 01193400 143 4 3929F49C-54BE-41CB-918C-147A1775AFE1

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-03-08 : 07:57:41

select * from
(select *, row_number() over (partition by SerialPrefix order by Occurs DESC) as rnum from TAC)dt
where rnum=1

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

olibara
Yak Posting Veteran

94 Posts

Posted - 2012-03-08 : 08:25:33

Great

Thanks a lot WebFred

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources