Help Aggregate Function - 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

Help Aggregate Function

Author

Topic

learntsql

524 Posts

Posted - 2009-08-27 : 03:32:48

Hi ALL,
My result set gives the following output

RowNum,State,Category,Item,Total
1,AP,C1,Item1,10
2,AP,C1,Item1,22
....
....
....

Now How can i display the record which has maximum RowNUM
like
2,AP,C1,Item1,22

please tell me

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-08-27 : 03:34:10

select * from your_table
where rownum=(select max(rownum) from your_table)

Madhivanan

Failing to plan is Planning to fail

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-08-27 : 04:28:17

select t.* from tablename t
inner join (select max(rownum) as rownum from tablename) s on s.rownum = t.rownum

learntsql

524 Posts

Posted - 2009-08-27 : 04:48:35

when i try to execute its giving only one record i.e max record but
Sorry i didnt mention but
my result set contains
i have various groups,various states,Categories and Items
i.e the same categories are repeated for many States.
for eg;
RowNum,State,Category,Item,Total
1,AP,C1,Item1,10
2,AP,C1,Item1,22(10+12)
1,AP,C1,Item2,2
1,AP,C2,Item1,23
2,AP,C2,Item1,30(23+7)
...
...
1,MH,C1,Item1,11
1,MH,C2,Item1,22
..
..
..

My Output Should like as follows
RowNum,State,Category,Item,Total
2,AP,C1,Item1,22(10+12)
1,AP,C1,Item2,2
2,AP,C2,Item1,30(23+7)
1,MH,C1,Item1,11
1,MH,C2,Item1,22
...
...

am i clear?
please tell me the sol.

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-08-27 : 04:56:30

select t.* from tablename t
inner join (select State,Category,Item,max(rownum) as rownum from tablename group by State,Category,Item) s on s.rownum = t.rownum

learntsql

524 Posts

Posted - 2009-08-27 : 06:26:21

ThankQ All
it does.

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-08-27 : 06:35:44

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources