Selecting most recent from mixed records

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)

Selecting most recent from mixed records

Author

Topic

mattt
Posting Yak Master

194 Posts

Posted - 2007-10-19 : 05:44:24

Hi,

I have a table which contains web content. Each row stores, alongside the markup, a location number, a version number and a modified date. Each time the content of a location is updated a new record is written to the table for that location with a new version number and modified date. In other words the table contains many versions of many locations.

What I need to do is write a query to "roll back" all the locations in the table to a date which is the closest match to a chosen version of a particular location, after first saving the current versions for posterity.

In order to do this, clearly, I need to be able to select a single version for each location where the modified date of that location is the most recent available in comparison with the modified date of a given version.

This query will pull out the max date for each location.

SELECT *
FROM ContentRepository
WHERE modified IN (
select max(modified) as modified from contentrepository where modified <= 
(select modified from contentrepository where locationID = 1 and version = 35)
group by locationID
)

However, what it returns is not guaranteed to be unique given the (unlikely, but possible) event that two date/times in the column match on different locations.

Can anyone improve on this?

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-10-19 : 06:01:11

We can't really give you correct answers unless you show us your table structure, sample data and expected output.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

mattt
Posting Yak Master

194 Posts

Posted - 2007-10-19 : 06:24:21

Table structure:


CREATE TABLE [dbo].[ContentRepository] (
	[locationID] [int] NOT NULL ,
	[version] [int] NOT NULL ,
	[markup] [text] COLLATE Latin1_General_CI_AS NULL ,
	[notes] [varchar] (1000) COLLATE Latin1_General_CI_AS NULL ,
	[editorID] [int] NOT NULL ,
	[modified] [datetime] NOT NULL 
)

Sample data (ignoring fields which don't matter in query):


locationID  version     modified                                               
------------------------------------------------------ 
1           1           2007-10-12 11:35:33.000
1           6           2007-10-15 16:27:33.000
1           46          2007-10-18 16:10:26.000
1           47          2007-10-18 16:17:51.000
1           60          2007-10-19 09:18:10.000
1           61          2007-10-19 10:10:53.000
2           1           2007-10-14 11:35:33.000
2           4           2007-10-14 11:35:33.000
2           11          2007-10-18 15:27:33.000
2           12          2007-10-18 16:13:21.000
3           8           2007-10-18 15:29:12.000
3           9           2007-10-15 14:24:54.000
4           6           2007-10-19 09:07:41.000
4           7           2007-10-18 09:08:01.000
5           1           2007-10-14 11:35:33.000

What I would be after is something like this:


locationID  version     modified                                               
------------------------------------------------------ 
1           61          2007-10-19 10:10:53.000
2           12          2007-10-18 16:13:21.000
3           8           2007-10-18 15:29:12.000
4           6           2007-10-19 09:07:41.000
5           1           2007-10-14 11:35:33.000

The query I've outlined above will do this, but only if all the values in the modified column of the table are unique, which we can't guarantee.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources