I would like to extract for a particular part number what is the highest version and revision, and corresponding serial number.
SLNO PARTNO VER REVISION P00026 1265 A 1.1 P00027 1265 A 1.1.1 P00028 1265 B 1 P00029 1265 B 1.1 P00030 1270 A 1.2 P00031 1270 A 1.3
For example, part number 1265 has B as highest version and 1.1 as highest revision of B, so it should show like this...
SLNO PARTNO VER REVISION P00029 1265 B 1.1
How do I do this in SQL? Thank you.
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).
This was first published in April 2007