CODEWITHSUNDEEP
databasems-access
Pablo
Pablo

Reputation: 29519

Sorting string in Access DB

I have a DB with field Version. There might be many records with the same version string, so to pull out versions I have, I do the following:

SELECT DISTINCT tblSample.Version
FROM tblSample
ORDER BY tblSample.Version DESC;

Here is sample data:

tblSample.Version
1.1.1000
1.2.1654
1.15.1223
1.2.1654
1.1.1000

What I get after executing query:

1.2.1654
1.15.1223
1.1.1000

What I want to get is(because 15 is obviously > 2 and 1):

1.15.1223
1.2.1654
1.1.1000

Respectively other octets should be considered as well.

Upvotes: 2

Views: 1270

Answers (4)

reiniero
reiniero

Reputation: 428

One solution would be: parse the strings that you get so you extract the numerical values, place leading 0s in front when required, and output a string. E.g. 1.2.1654 becomes 0001.0002.1654 (if you're sure the numbering won't exceed 4 characters). 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) Then you can sort on this.

Write a VBA function that does this for you, called e.g. NormalizeVersion, then call it in your query, such as (air code):

SELECT DISTINCT NormalizeVersion(tblSample.Version)
FROM tblSample
ORDER BY NormalizeVersion(tblSample.Version) DESC;

(code fixed after comment)

Upvotes: 0

HansUp
HansUp

Reputation: 97101

Store Version as 3 numeric fields.

major minor release
    1     1    1000
    1     2    1654
    1    15    1223
    1     2    1654
    1     1    1000

Then you can generate Version, sorted as you wish, with a query whenever you 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;

Upvotes: 2

Conrad Frix
Conrad Frix

Reputation: 52645

Basically if you want to sort on x and do a distinct on y you need to do the distinct first (hence the subquery)

Using the following function (modified from fredg's answer in this thread) Note: the function uses the split function which I think is appropriate here.

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 can 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 also need to cast to an int to get the sorting correctly. You can either do this in the SQL (as my updated answer has) or you can do this in ParseText and return an int

Upvotes: 2

mwolfe02
mwolfe02

Reputation: 24227

What you are trying to do is analogous to sorting IP addresses. You can follow the exact same techniques outlined in that article to accomplish what you are after.

Upvotes: 2

Related Questions