Max value of one Identity filed

[shahab]

I have a table with Id field.it is identity and has many values, not i need the greatest value of identity...

Notice:The answer must be one record not two or more ;)

How can i select the biggest value of Id field?

For example:

SELECT MAX(Id) ,manyfields
from tablename
Group by manyfields

//****************************************************

[This shows the all of rows & fiels! :eek:

http://doost.europe.webmatrixhosting.net/sql/max01.gif

I want to see only 7 :)

Edited March 13, 2004 by shahab

[Robby]

try this:

 

select max(newsid) as mID from tblNews

 

If you use the Group By then you will get the max of each group

[shahab]

try this:

 

select max(newsid) as mID from tblNews

thanks I knew that but iawnt to see the biggest record id and the oder fiels too!!

this query shows only 7 but i want to see all of fields that their id is 7!

[PlausiblyDamp]

SELECT Id ,manyfields

from tablename

where Id = (SELECT MAX(ID) from tablename)

 

edit: typo

Edited March 14, 2004 by PlausiblyDamp

[shahab]

Super Moderator:

Thanks,I find another way:

select NewsId , subject

from tblNews

where

NewsId in(select Max(NewsId) from tblNews)