Take the 2-minute tour ×

I have an "Observation" table in SQL Server 2008. This table has a locationId column for a bunch of geographic locations, a few columns for observation details and a column for latest updated date.

Every week, a new observation record for each location is appended. So a location has many occurrences in the table.

What I want to achieve is to be able to get the most recent observation record for each location.

Can anyone help with any idea?

share|improve this question

3 Answers 3

 select * from observation where date=(select max(date) from observation)


select top 1 * from observation order by date desc
share|improve this answer
It would have to be SELECT TOP 1 * FROM ..... (note: space and not a dot between 1 and *) –  marc_s Nov 5 '12 at 5:59
@marc_s thank you, I didnt notice –  SRIRAM Nov 5 '12 at 6:03

Run query

select * from Observation 
group by location 
order by viewdate desc

Please also give the full details about table and what you want to get.

EDIT : Backtick removed.

share|improve this answer
This is not valid for SQL Server .... –  marc_s Nov 5 '12 at 5:58
can you tell me what not valid please... –  Bajrang Nov 5 '12 at 6:01
All those backticks - around the table name and the column names. That's MySQL - not SQL Server. –  marc_s Nov 5 '12 at 6:05
Thank you @Marc_s –  Bajrang Nov 5 '12 at 6:07
If you are really interested in finding a correct (working) solution for SQL Server and you do not have a SQL Server instance handy, consider using SQLFiddle for simple tests. –  Andriy M Nov 5 '12 at 6:54
select a.* from observations a inner join 
(select locationid ,max(updateddate) dates  from observations
group by locationid) b
on a.locationid=b.locationid
and a.updateddate=b.dates  
share|improve this answer

Your Answer


