Choose the greatest field - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Choose the greatest field

Author

Topic

Exir
Posting Yak Master

151 Posts

Posted - 2008-12-01 : 00:20:36

I have some rows in my table wich have same field values and only one field is different between them (the id field).
How can write a select query to choose the greatest ID among those rows?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-01 : 00:33:44

[code]select * from yourtable where id=select max(id) from yourtable[/code]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-12-01 : 02:01:48

select * from yourtable where id=(select max(id) from yourtable)

Madhivanan

Failing to plan is Planning to fail

soorajtnpki
Posting Yak Master

231 Posts

Posted - 2008-12-01 : 02:20:34

hi exir,
if ur table has only one 'same field values' group,
then use select max(id) from ur_table or above posted replies..

or if ur table groups of duplicate values,
then try
select max(id) as Id,col from ur_table group by col
the second solution will fit to all cases

ok TANX.....

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-01 : 02:40:56

quote:
Originally posted by soorajtnpki

hi exir,
if ur table has only one 'same field values' group,
then use select max(id) from ur_table or above posted replies..

or if ur table groups of duplicate values,
then try
select max(id) as Id,col from ur_table group by col
the second solution will fit to all cases

ok TANX.....

thats will just return max id value for each col value group. for getting whole data you need either

select t.*
from yourtable t
inner join (select max(id) as Id,col from ur_table  group by col)t1
on t1.Id=t.id
and t1.col=t.col

if sql 2000 and

select * from
(
select row_number() over (partition by col order by id desc) AS seq,*
from yourtable 
)t
where seq=1

if sql 2005

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources