Complex SQL selection? 1

[furtivevole]

I have a table holding info about software builds, part of which - very simplified - looks like this:

Product Version Release Other fields
Prod_1 1 1 Data for rel 1
Prod_1 1 2 Data for re1 2
Prod_1 1 3 etc....
Prod_1 2 1
Prod_1 2 2
Prod_2 1 1
Prod_2 1 2
Prod_2 1 3
Prod_2 1 4

We are generally only interested in the most recent (i.e. highest) release for each product/version combination. The final output should therefore look like this:

Product Version Release Other fields
Prod_1 1 3 etc....
Prod_1 2 2
Prod_2 1 4

I won't bore you with all the things I've tried over the last few hours, but unfortunately my SQL is both minimal and rusty, and nothing has worked so far. Could anyone give any ideas?
Many thanks.

 

[SkipVought]

Hi,

Select Product, Version, Max(Release)
From MyTable
Group By Product, Version

Skip,

_{[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM!}

 

[PHV]

You may try something like this:
SELECT A.Product, A.Version, A.Release, A.OtherFields
FROM yourTable A INNER JOIN (
SELECT Product, LastVersion, Max(Release) As LastRelease FROM (SELECT Product, Max(Version) As LastVersion FROM yourTable GROUP BY Product) V
GROUP BY Product, LastVersion
) R ON A.Product=R.Product And A.Version=R.LastVersion And A.Release=R.LastRelease
;
If Version and Release are both numeric, we may simplify the query.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[SkipVought]

Duh! Where was my head????

Skip,

_{[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM!}

 

[furtivevole]

Hi PH

Thanks for that! I can follow your thinking here, although unfortunately it refused to work in practice ("syntax errors" - and 'V' is a typo ?). However I got round the problem with:

Query Q_sel1
SELECT TempTable.product, TempTable.version, Max(TempTable.release) AS MaxRel
FROM A AS TempTable
GROUP BY TempTable.product, TempTable.version;

(useful as a stand-alone query for other purposes anyway)

followed by:
Query Q_join
SELECT A.*
FROM Spec_data INNER JOIN Q_sel1 ON (A.product = Q_sel1.product) AND (A.version = Q_sel1.version) AND (A.release = Q_sel1.MaxRel);

which gives the required results. BTW I'm assuming that when Q_join is run, that Q_sel1 is executed in real-time and therefore this is equivalent to the single query that you'd suggested.

Linnet