2

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.

Improve this question
1
  • 1
    Your data is improperly structured if you need to sort by sub-strings. Commented May 19, 2011 at 15:56

4 Answers 4

Reset to default
2

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

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Unless I missed something, this answer will still not sort 1.15.1223 before 1.2.1654 in DESC order.
+1 But would you actually use this approach, CF? Seems to me the 3 components of Version rightfully belong in separate numeric fields. This Version attribute smells like one of those identifiers folks create by concatenating year, Project_ID, and so forth. If you want that, don't store it; store the sub-elements, and do the concatenation on demand as needed.
@HansUp. I agree that the probably should be three separate fields, but regrettably the cost of doing so may not justify the value of that change.
As long as the data is not properly stored, you're going to risk performance problems, since the ORDER BY clause above is not sargable.
2

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.

Improve this answer

2 Comments

They are not querying distinct values... I guess this is the key point.
Then you should be able to integrate Conrad Frix's suggestion to put the Distinct in a subquery. To make it easier to work with you could save that subquery as a standalone query. Treat the saved query as your base table and everything in the link I provided should work from there.
2

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;
Improve this answer

1 Comment

Your solution is also good, but I can't alter DB structure at this moment. Thanks!
0

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)

Improve this answer

9 Comments

I got an error ORDER BY clause (NormalizeVersion(tblSample.Version)) conflicts with DISTINCT
That's why I said it's air code. So it probably is something like: SELECT DISTINCT Normalizeversion(tblSample.Version) FROM tblSample ORDER BY NormalizeVersion(tblSample.Version) DESC;
? It does work here in a sample db I just ran on Access XP. See dl.dropbox.com/u/20995033/test.mdb (with dummy VB function though)
OK but how it solves the issue? I still need to get rid of those padding zeroes. If I give this query as a source to list box(which is one of the places I represent this data), then it will be not so easy. I would really prefer to have some kind of predicate which will compare two versions. Is there any way?
Format(Elements(Counter), "00000") executes faster than Right("0000" & Elements(Counter), 5)
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.