What am I doing with 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

What am I doing with with Stored Procedure

Author

Topic

JJ297
Aged Yak Warrior

940 Posts

Posted - 2011-05-10 : 11:16:11

I have this query

select max(maxseq) as maxseq, cos, clm, org_id, typ
From test
group by cos, clm, org_id, typ

that produces this table


MaxNum   COS     CLM    Org_id  Typ
2	209590	209590	029	R
3	209590	209590	029	T
1	209590	209590	219	T
4	209590	209590	PC2	R
5	209590	209590	PC2	T

What I want is this:



I want to get this result:

MaxNum   Cos    CLM    org_id  Type
5	209590	209590	PC2	T



How do I get just the maxnum to show up which is 5? 

I tried this query and get the same results

SELECT cos, clm, org_id, typ,maxseq
FROM
(
select cos, clm, org_id, typ, maxseq, row_number() over(partition by cos, clm, org_id, typ order by maxseq desc) as rown

 From test

) t1
WHERE rown = 1 
ORDER BY maxseq

What am I missing?

here's the table info:


CREATE TABLE [dbo].[test](
[MaxSeq] [smallint] NOT NULL,
[COS] [char](6) NOT NULL,	
[CLM] [char](6) NOT NULL,
	
	
	[ORG_ID] [varchar](4) NOT NULL,
	[TYP] [char](1) NOT NULL,
		[t2jurisid] [int] IDENTITY(1,1) NOT NULL)

Insert into test

Select '2', '209590', '209590', '029', 'R' union all
Select '3', '209590', '209590', '029', 'T' union all
Select '1', '209590', '209590', '219', 'T' union all
Select '4', '209590', '209590', 'PC2', 'R' union all
Select '5', '209590', '209590', 'PC2', 'T'

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-05-10 : 11:24:07

select maxseq , [cos], clm, org_id, typ
From test

where maxseq = (select max(maxseq) from test)

Jim

Everyday I learn something that somebody else already knew

JJ297
Aged Yak Warrior

940 Posts

Posted - 2011-05-10 : 11:42:12

Thanks Jim!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources