Max 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

Max function

Author

Topic

pvccaz
Yak Posting Veteran

87 Posts

Posted - 2009-02-23 : 12:41:33

Hi,

I have a table with 3 cols:
Id Number expense
12 Sam 56
13 Sam 89
78 Sam NULL

I want to get the row which has the maximum id and the columns that
are assoicated with it. (The Id is a unique column).

Output should be:

Id Number expense
78 Sam NULL

I am trying to use the max function with the group.. but having trouble getting the
correct result. Can any ideas please?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-23 : 12:48:51

select id, number, expense
from (
select id, number, expense, row_number() over (partition by number order by id) as recid
from table1
) as f
where recid = 1

E 12°55'05.63"
N 56°04'39.26"

pvccaz
Yak Posting Veteran

87 Posts

Posted - 2009-02-23 : 13:01:45

Thanks so much Peso..!!

Since i wanted the maximum id i just changed the query to include descending order. "partition by number order by id desc" . It gives the max id.

Thanks much!.

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-02-23 : 23:16:36

Also Try This,

declare @temp table (Id int, Number varchar(32), expense int)
insert into @temp
select 12, 'Sam', 56 union all
select 13, 'Sam', 89 union all
select 78, 'Sam', NULL

select maxid,number,expense from @temp t
inner join ( select max(id) maxid from @temp) t1 on t.id = t1.maxid

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-24 : 09:18:18

quote:
Originally posted by Nageswar9

Also Try This,

declare @temp table (Id int, Number varchar(32), expense int)
insert into @temp
select 12, 'Sam', 56 union all
select 13, 'Sam', 89 union all
select 78, 'Sam', NULL

select maxid,number,expense from @temp t
inner join ( select number,max(id) maxid from @temp) t1 on t.id = t1.maxid and t.number=t1.number

small modification if op is looking for max id corresponding to each number

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources