query to display only most recent version.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi there,

Here's one that's bugging me, I'm developing a quotes system and I have a
table containing job quotes that can contain more than one record (version of
a quote) per quote number (I need to maintain a full audit trail of previous
quote versions)

The table is, in very simplistic terms structured like;

|| QuoteID (AutoNum PK) || Quote Number (Num PK) || VersionNumber (Num PK)
|| All the other fields...

Obviously there's more to it than that but for the purposes of this question
it'll do.

What I need to do is create a query that will select only the highest
version of each quote number.

I can't for the life of me figure this one out. I'm guessing I need to be
looking at the general query properties but I seem to have hit an
inspirational brick wall. Can anyone point me in the right direction?
 
Try this

SELECT M1.[Quote Number], M1.VersionNumber
FROM TableName AS M1
WHERE M1.VersionNumber In (SELECT Top 1 M2.VersionNumber
FROM TableName as M2
WHERE M2.[Quote Number]=M1.[Quote Number]
ORDER BY M2.VersionNumber Desc)
 
Thanks Ofer,

Sorry for the delay in response (UK time.. it was late!)

Your suggestion worked a treat (once I'd set the general query properties to
display unique values!)

Thanks again..
--
R.Douthwaite.

Ofer said:
Try this

SELECT M1.[Quote Number], M1.VersionNumber
FROM TableName AS M1
WHERE M1.VersionNumber In (SELECT Top 1 M2.VersionNumber
FROM TableName as M2
WHERE M2.[Quote Number]=M1.[Quote Number]
ORDER BY M2.VersionNumber Desc)


--
I hope that helped
Good luck


R.Douthwaite said:
Hi there,

Here's one that's bugging me, I'm developing a quotes system and I have a
table containing job quotes that can contain more than one record (version of
a quote) per quote number (I need to maintain a full audit trail of previous
quote versions)

The table is, in very simplistic terms structured like;

|| QuoteID (AutoNum PK) || Quote Number (Num PK) || VersionNumber (Num PK)
|| All the other fields...

Obviously there's more to it than that but for the purposes of this question
it'll do.

What I need to do is create a query that will select only the highest
version of each quote number.

I can't for the life of me figure this one out. I'm guessing I need to be
looking at the general query properties but I seem to have hit an
inspirational brick wall. Can anyone point me in the right direction?
Click to expand...
Click to expand...
 
Back
Top