SELECTing considering Revision Dates

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 considering Revision Dates

Author

Topic

DevinDow
Starting Member

1 Post

Posted - 2008-12-30 : 13:51:00

I am fairly new to SQL, but I am advancing quite well.

I am modifying a table to store previous revisions of an item. I have come up with a new schema that can store this (feel free to offer reccomendations here too), but now I need help writing a Query that takes into account the Revisions.

Original Schema
---------------
Items Table
- ID (int) Primary Key
- Type (int) Foreign Key - can NOT change per revision
- Name (string) - can change per revision
- other fields - can change per revision

New Schema
----------
Items Table
- ID (int) Primary Key
- Type (int) Foreign Key

ItemFields Table
- ID (int) Foreign Key to Item Table
- Name (string)
- other fields
- RevDate (datetime)

I used to query all the Items simply:
SELECT * FROM Items
ID__Type__Name
1___1_____'Item1'
2___1_____'Item2'

Now I want to select only the last revision of each Item.
SELECT * FROM ItemFields
ID__Name_____RevDate
1___'Item1'___12/1/08
1___'MyItem'__12/30/08
2___'Item2'___12/1/08
... should return two items, 'MyItem' & 'Item2'.

How do I write this query?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-12-30 : 13:53:21

SELECT Name, MAX(RevDate) AS RevDate
FROM ItemFields
GROUP BY Name

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-30 : 14:03:37

[code]SELECT i.*
FROM ItemFields i
INNER JOIN (SELECT ID,MAX(RevDate) AS Latest
FROM ItemFields
GROUP BY ID)i1
ON i1.ID=i.ID
AND i1.Latest=i.RevDate
[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-30 : 14:05:33

or even this


SELECT t.ID,t.Name,t.RevDate
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY ID ORDER BY RevDate DESC) AS Seq,*
FROM ItemFields
)t
WHERE t.Seq=1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources