use of groupby and get 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

General SQL Server Forums

New to SQL Server Programming

use of groupby and get max val

Author

Topic

shanmugaraj
Posting Yak Master

219 Posts

Posted - 2007-02-10 : 01:12:19

Respected Friends,
kindly solve my query.

Table Structrue:
Table Name : Custom_Table_SPPR_Master
Fields :
Sno int pk
SPPR_No int pk
Revison_No int pk
rev_Date date
prop_amt decimal
claim_amt decimal
avail_amt decimal
Status int

Data
Sno SPPR_No Revison_No rev_Date prop_amtclaim_amtavail_amtStatus
1 2000 0 2006-10-01 00:00:00.000 1000 600 400 2
2 2000 1 2006-10-01 00:00:00.000 1100 600 500 2
3 2000 2 2006-10-01 00:00:00.000 1400 600 800 2
4 2000 3 2006-10-01 00:00:00.000 1500 900 600 2
5 2001 0 2006-10-01 00:00:00.000 1000 200 800 2
6 2001 1 2006-10-01 00:00:00.000 1200 600 600 2
7 2002 0 2006-10-01 00:00:00.000 1000 600 400 2
8 2002 1 2006-10-01 00:00:00.000 1100 600 500 2
9 2003 0 2006-10-01 00:00:00.000 1000 600 400 2

I want the record where max of the revision no is seen for each sppr
that is the result should the rows of sno: 4,6,8,9

i tried the following but in vein

select max(revision_no) ,sppr_no from Custom_Table_SPPR_Master group by sppr_no
-- in this i am getting records, but i want * datas

select distinct(sppr_no) from Custom_Table_SPPR_Master where revision_no in(select max(revision_no) from Custom_Table_SPPR_Master group by revision_no) group by sppr_no

select sppr_no,count(revision_no) from Custom_Table_SPPR_Master where revision_no in(select max(revision_no) from Custom_Table_SPPR_Master group by revision_no)
group by sppr_no,revision_no

Kindly let me the answers . thanks in advance.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-10 : 02:22:29

[code]
declare @Custom_Table_SPPR_Master table
(
Sno int,
SPPR_No int,
Revison_No int,
rev_Date datetime,
prop_amt int,
claim_amt int,
avail_amt int,
Status int
)
insert into @Custom_Table_SPPR_Master
select 1, 2000, 0, '2006-10-01', 1000, 600, 400, 2 union all
select 2, 2000, 1, '2006-10-01', 1100, 600, 500, 2 union all
select 3, 2000, 2, '2006-10-01', 1400, 600, 800, 2 union all
select 4, 2000, 3, '2006-10-01', 1500, 900, 600, 2 union all
select 5, 2001, 0, '2006-10-01', 1000, 200, 800, 2 union all
select 6, 2001, 1, '2006-10-01', 1200, 600, 600, 2 union all
select 7, 2002, 0, '2006-10-01', 1000, 600, 400, 2 union all
select 8, 2002, 1, '2006-10-01', 1100, 600, 500, 2 union all
select 9, 2003, 0, '2006-10-01', 1000, 600, 400, 2

select *
from @Custom_Table_SPPR_Master t
inner join
(
select SPPR_No, Revison_No = max(Revison_No)
from @Custom_Table_SPPR_Master t
group by SPPR_No
) m
on t.SPPR_No = m.SPPR_No
and t.Revison_No = m.Revison_No
order by Sno
[/code]

shanmugaraj
Posting Yak Master

219 Posts

Posted - 2007-02-10 : 03:53:26

Thanks a Lot khtan. i have got the result.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources