Selecting Multiple Columns Based on MAX

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)

Selecting Multiple Columns Based on MAX

Author

Topic

ramoneguru
Yak Posting Veteran

69 Posts

Posted - 2006-06-20 : 18:34:03

I wrote a simple query to get a few columns from the last record entered in a table. Is this normally how it is done:

SELECT LastSurname, FirstName
FROM Person
WHERE PersonID =
(SELECT MAX(PersonID)
FROM Person)

Or is there a better way I'm not yet aware of?
--Nick

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-06-20 : 18:46:27

That is one way. Here is another:


SELECT p.LastSurname, p.FirstName
FROM Person p
INNER JOIN 
(
	SELECT MAX(PersonID) AS PersonID
	FROM Person
) t
ON p.PersonID = t.PersonID

When the queries get a bit more complex, you might need to use the derived table approach instead.

Tara Kizer
aka tduggan

cvraghu
Posting Yak Master

187 Posts

Posted - 2006-06-21 : 17:19:25

Another option is -

select top 1 LastSurname, FirstName
FROM Person
order by personid desc

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-06-22 : 05:08:57

quote:
Originally posted by cvraghu

Another option is -

select top 1 LastSurname, FirstName
FROM Person
order by personid desc

That wont give all rows if there are multiple rows for Max(PersonId)

Madhivanan

Failing to plan is Planning to fail

cvraghu
Posting Yak Master

187 Posts

Posted - 2006-06-22 : 14:01:06

You are correct. But looking at the request, i think, only one row is expected.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources