I've got a DB with area Version. There can be many records with similar version string, to take out versions I've, I actually do the next:
SELECT DISTINCT tblSample.Version FROM tblSample ORDER BY tblSample.Version DESC;
Here's sample data:
tblSample.Version 1.1.1000 1.2.1654 1.15.1223 1.2.1654 1.1.1000
Things I get after performing query:
1.2.1654 1.15.1223 1.1.1000
What I wish to get is(because 15 is clearly > 2 and 1):
1.15.1223 1.2.1654 1.1.1000
Correspondingly other octets should be thought about too.
What you're attempting to do is similar to sorting IP addresses. You are able to stick to the identical techniques layed out for the reason that article to complete what you're after.
Essentially if you wish to
sort on x and perform a
distinct on y you must do the distinct first (hence the subquery)
Public Function ParseText(TextIn As String, X) As Variant On Error Resume Next Dim var As Variant var = Split(TextIn, ".", -1) ParseText = var(X) End Function
You are able to write this SQL Statement
SELECT t.Version FROM ( SELECT Distinct tblSample.Version FROM tblSample.Version) t ORDER BY Cint(ParseText([t.Version],0)) DESC , Cint(ParseText([t.Version],1)) DESC , Cint(ParseText([t.Version],2)) DESC;
Upate As HansUp noted. You should also cast for an int to obtain the sorting properly. You may either do that within the SQL (as my up-to-date answer has) or this can be done in ParseText and return an int
Store Version as 3 number fields.
major minor release 1 1 1000 1 2 1654 1 15 1223 1 2 1654 1 1 1000
You'll be able to generate Version, sorted as you want, having a query whenever you really need it.
SELECT [major] & "." & [minor] & "." & [release] AS Version_num FROM [SELECT DISTINCT major, minor, release FROM tblSample]. AS s ORDER BY s.major DESC , s.minor DESC , s.release DESC;
One solution could be: parse the strings that you will get which means you extract the statistical values, place leading 0s in-front when needed, and output a string. E.g. 1.2.1654 becomes 0001.0002.1654 (if you are sure the numbering will not exceed 4 figures). Code:
Function NormalizeVersion(Inputstring As String) As String ' Creates sortable strings out of version numbers such as 1.6.222 Dim Elements() As String Dim Counter As Integer Dim Result As String Elements = Split(Inputstring, ".") For Counter = 0 To UBound(Elements) Select Case Counter Case 0 'First element Result = Format(Elements(Counter), "00000") Case Else 'Followups Result = Result & "." & Right("0000" & Elements(Counter), 5) End Select Next Counter NormalizeVersion = Result End Function
(incorporated HansUp's performance improvement) You'll be able to sort about this.
Write a VBA function that performs this for you personally, known as e.g. NormalizeVersion, then refer to it as inside your query, for example (air code):
SELECT DISTINCT NormalizeVersion(tblSample.Version) FROM tblSample ORDER BY NormalizeVersion(tblSample.Version) DESC;
(code fixed after comment)