Select max field value for a unique id

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

SQL Server 2000 Forums

Transact-SQL (2000)

Select max field value for a unique id

Author

Topic

Rliss
Starting Member

31 Posts

Posted - 2006-07-26 : 12:56:43

We have a table that has 3 fields. 1st field is the id field, the 2nd field contains a value that shows the last time that the record was updated, and the last field contains random data. So, we'll have a table such as this:

id ctl data
--- ----- -----
1 1 2132
1 2 3211
1 3 2514
2 1 5213
2 2 6542
3 1 6423

We are trying to select the where we have a unique id value with the maximum ctl value only. Therefore, we want to return the following:

id ctl data
--- ----- -----
1 3 2514
2 2 6542
3 1 6423

We have tried:
select max(ctl), id, data from table group by id, data
We have also tried several different selects without being able to get the desired effect.

Thanks for the help.

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-07-26 : 13:09:47

Here's one way...

--data
declare @t table (id int, ctl int, data int)
insert @t
          select 1, 1, 2132
union all select 1, 2, 3211
union all select 1, 3, 2514
union all select 2, 1, 5213
union all select 2, 2, 6542
union all select 3, 1, 6423

--calculation
select * from @t a
where ctl = (select max(ctl) from @t where id = a.id) order by id

/*results
id          ctl         data        
----------- ----------- ----------- 
1           3           2514
2           2           6542
3           1           6423
*/

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-07-27 : 03:49:55

or this....

declare @t table (id int, ctl int, data int)
insert @t
          select 1, 1, 2132
union all select 1, 2, 3211
union all select 1, 3, 2514
union all select 2, 1, 5213
union all select 2, 2, 6542
union all select 3, 1, 6423

select t1.* from @t as t1 join (select id, max(ctl) as ctl from @t
group by id) as t2 on t1.id = t2.id and t1.ctl = t2.ctl order by t1.id

Harsh Athalye
India.
"Nothing is Impossible"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-07-27 : 09:00:12

or

Select * from yourTable
where ctl in (select max(ctl) from yourTable group by id)

Madhivanan

Failing to plan is Planning to fail

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-07-27 : 09:18:22

quote:
Select * from yourTable
where ctl in (select max(ctl) from yourTable group by id)

That won't work in this case, Madhivanan, because ctl is not unique...

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-07-27 : 09:27:48

Thanks Ryan. i didnt notice that

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources