|
Nice question. Here's one approach:
select P.slno
, P.partno
, P.ver
, P.revision
from (
select partno
, max(ver) as maxver
from parts
group
by partno
) as V
inner
join (
select partno
, ver
, max(revision) as maxrevision
from parts
group
by partno
, ver
) as R
on R.partno = V.partno
and R.ver = V.maxver
inner
join parts as P
on P.partno = R.partno
and P.ver = R.ver
and P.revision = R.maxrevision
Note that this will fail if any of your revision
or subrevision numbers goes beyond a single digit. Remember that
string columns sort from left to right, not numerically, so here's
how they would sort if there were 12 revisions:
1.1
1.10
1.11
1.12
1.2
1.3
1.4
1.5
1.6
1.7
1.8
1.9
Of course, the query would still be correct! However, you
wouldn't get what you thought was the highest (1.12),
you'd get what actually was the highest (1.9).
|
