Group by query with Maximum value

[murfeezlaw]

I'm trying to build a Group By query to pull the record with the latest value of the Version.



CompanyID can vary. I want the record with the latest value of Version which will have a value of A, B, C etc...

For example I would only want to pull the record with Version D showing me CompanyID RM0060

QuoteID	Version	CompanyID
0800323	D	RM0060
0800323	C	RM0060
0800323	B	RM0060
0800323	A	RM0012

Any ideas? I tried using the DMax function and it seems to just hang. It takes a long time since it's a big table.

[CJ_London]

It takes a long time since it's a big table.

and probably not indexed? Actually I don't think dmax uses indexing anyway and not sure why you need a groupby query to return a single record

try something like

SELECT TOP 1 *
FROM myTable
WHERE QuoteID='0800323'
ORDER BY Version Desc

[murfeezlaw]

Thanks! that's really close. How would I modify that so that I don't specify a QuoteID? There are many records with the same QuoteID and different Versions.

[CJ_London]

you mean you want the max version for all quoteID's? If so then you need a different query

[murfeezlaw]

Yes, exactly. I narrowed the records and indexed the table but it still takes a little bit. Do you have any idea how to run it differently?

[CJ_London]

then you need this query - note the aliasing

SELECT O.*
FROM myTable AS O INNER JOIN (SELECT QuoteID, max(Version) AS Latest FROM myTable GROUP BY QuoteID) AS L
ON O.QuoteID=L.QuoteID AND O.Version=L.Version

[murfeezlaw]

Thank you very much. So O=mytable or dbo_Version. I'm getting an error on L though. What is L an alias for?

[CJ_London]

L is alias for the subquery (the bit in brackets). Aliases can be whatever you want within normal naming conventions, in this case, for me O stands for 'Objective Data' and L for 'Latest Data'. Could just as easily used X and Y or Target and Maxversion, just something that makes sense to you. I use them all the time to make sql easier to read and cut down on typing.

With regards your error - what is it? and copy and paste the sql you have actually used that generates the error.

[murfeezlaw]

Right, that's what I thought. Sorry my SQL is a little weak.

Here is my SQL code:
SELECT O.*
FROM Version AS O INNER JOIN (SELECT QuoteID, max(Version) AS Latest FROM Version GROUP BY QuoteID) AS L
ON O.QuoteID=L.QuoteID AND O.Version=L.Version

I'm not actually getting an error now. It's prompting me for O.Version and L.Version

Thank you so much for your time!

[CJ_London]

Version is either a table name or a field name, it can't be both. You mentioned dbo_version before to replace highlighted in red?

FROM Version AS O INNER JOIN (SELECT QuoteID, max(Version) AS Latest FROM Version