Please help with stored procedure

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

Please help with stored procedure

Author

Topic

JJ297
Aged Yak Warrior

940 Posts

Posted - 2011-04-22 : 14:51:22

This stored procedure

select area, max(maxseqnum) as maxseqnum, loc, clms
from test1
group by clms, area, loc

produces this table:


Area	Maxseqnum	Loc	        clms
02	0	       FieldOffice	215458
02	3	       Fieldoffice	543298
02	4	       DDS	        543298
02	4	       FieldOffice	658298
02	5	       DDS	        658298
02	8	       Fieldoffice	217298
02	9	       DDS	        217298

I want to get this instead by showing only one clms with the highest maxseqnum



Area	Maxseqnum	Loc	        clms
02	0	      FieldOffice	215458
02	4	      DDS	        543298
02	5	      DDS	        658298
02	9	      DDS	        217298

Here's the table info. Thanks so much!

CREATE TABLE [dbo].[test1](
[area] [varchar](2) NULL,
[maxseqnum] [smallint] NULL,
[loc] [varchar](50) NULL,
[clms] [char](6) NOT NULL,
) ON [PRIMARY]

GO

Insert into test1

Select '02', ' ', 'Fieldoffice', '215458' union all
Select '02', '3', 'Fieldoffice', '543298' union all
Select '02', '4', 'DDS', '543298' union all
Select '02', '4', 'Fieldoffice', '658298' union all
Select '02', '5', 'DDS', '658298' union all
Select '02', '8', 'Fieldoffice', '217298' union all
Select '02', '9', 'DDS', '217298'

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-04-22 : 15:04:07

SELECT *
FROM
(
select area,maxseqnum,loc,clms,row_number() over(partition by area,clms order by maxseqnum desc) as rown

from #test1
) t1
WHERE rown = 1
ORDER BY area,maxseqnum

Jim

Everyday I learn something that somebody else already knew

JJ297
Aged Yak Warrior

940 Posts

Posted - 2011-04-22 : 15:07:51

Thanks Jim!!!!

Please explain so I will know how to do this the next time I've been stuck all day. What's the over(partition etc... means?

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-04-22 : 15:17:20

Row_Number() is a windowed function ( rank() is another popular one) that says
"assign row numbers to the groups of area,clms that ordered by the maxseqnum desc", so that the biggest maxseqnum gets a value of 1, the next biggest 2, etc. In your example, change the are in the last two records to '03' and see what that does, it may help you see better what the row_number() is doing.

Jim

Everyday I learn something that somebody else already knew

JJ297
Aged Yak Warrior

940 Posts

Posted - 2011-04-22 : 15:59:17

Wow thanks so much for explaning to me I will read up further on it.

I added more fields to it and it's running slower any way to speed it up?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources