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).
Dig Deeper on Oracle and SQL
Related Q&A from Rudy Limeback
Read an example of an SQL case expression from our SQL expert Rudy Limeback. Continue Reading
Read SQL expert Rudy Limeback's advice for counting combinations in a table with SQL's GROUP BY clause Continue Reading
Read about the Mimer Validator, a tool used to verify your SQL code, in this tip from SQL expert Rudy Limeback. Continue Reading
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.