Selecting more recent child record with parent
-
Hello,
I have two tables in a 1-M relationship, Items and Versions. I'm trying to join the two such that I only have as many rows as there are in the Items table and the equivilent row from Versions is the most recent one.
The tables are as follows:
Items
------
PK Ref INT
...
Versions
--------
PK Ref INT
FK Item_Ref INT
DateCreated SMALLDATETIME
...
What would be the best way to achieve this? At the moment I'm unsure as to whether to use the Versions.Ref or Versions.DateCreated column to identify the latest one. For simplicity's sake I'll use Versions.Ref.
Hope someone can help!
Thanks
-
WITH VersionsRn AS (SELECT *,ROW_NUMBER() OVER(PARTITION BY Item_Ref ORDER BY Ref DESC) AS Rn
FROM Versions)
SELECT i.*,v.*
FROM Items i
INNER JOIN VersionsRn v ON v.Item_Ref=i.Ref AND v.Rn=1
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Thats a pretty cool query. I like it. Unfortunately, I've just discovered the box I need to run this query on is a SQL Server 2000 machine. WITH and ROW_NUMBER() is not supported. The box I usually use is 2005.
Is there a way to do this on SQL 2000? Should I repost in the other forum?
Thank you for your help!
-
SQL 2000 Version:
[font="Courier New"]SELECT I.*, V.*FROM Items I
INNER JOIN Versions V ON I.Ref = V.Item_Ref
INNER JOIN (SELECT Item_Ref, MAX(Ref) Ref FROM Versions GROUP BY Item_Ref) V2 ON V.Ref = V2.Ref
[/font]
This assumes that the highest Ref from Versions will always be the most recent one. If that's not correct, this needs to be reworked. As I have no sample data from you, this solution is not tested.
-
Thanks Garadin, works a treat 🙂
-
I've now found that the highest Versions.Ref does not necessarily equal the most recent row. As such I'm going to need to change the sub query such that it uses the max DateCreated field to get the most recent version row.
How could I change the sub query above to get the highest DateCreated yet still return Versions.Ref (which I need in the emcompassing join)?
Many thanks,
Rich
-
Anyone? I'm really stuck on this so any help or pointers would be greatly appreciated!
-
Rich Caine (12/10/2008)
How could I change the sub query above to get the highest DateCreated yet still return Versions.Ref (which I need in the emcompassing join)?If you change the subquery to give the highest datecreated, then the join must also be changed so that it joins back to versions on Item_ref and datecreated.
SELECT I.*, V.*
FROM Items I
INNER JOIN Versions V ON I.Ref = V.Item_Ref
INNER JOIN (SELECT Item_Ref, MAX(DateCreated) MaxDate FROM Versions GROUP BY Item_Ref) V2 ON V.DateCreated = V2.MaxDate and V.Item_Ref = V2.Item_Ref
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
select *from Items I
join Versions V
on I.Ref = V.Item_Ref
where v.DateCreated = (
select Max( DateCreated )
from Versions V2
where V2.Item_Ref = i.Ref
);
In my sig you will find a link to a Google group about database versioning. There you can download a document that discusses this query in detail. It may be more than what you need but you will find queries that will return not only the latest version, such as the one above, but also the version that was current as of a specified date and time. It will also give you the ability to work with future versions inserted ahead of time. Pretty cool -- if you need that kind of stuff.
Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms -
Thanks Gail, it all makes sense but I couldn't seem to create the right query myself.
Cheers!
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply