locked
Database SQL between Microsoft Access and Visual Studio, sorting? RRS feed

  • Question

  • How would I go about sorting this data?

    When I do the normal "ORDER BY  QuestionNumber ASC" It only considers the first character, then sorts by that. I.e 10 appears at the top of the list, when it should be at the bottom.

    How would I write an SQL statement to sort this correctly?

    Saturday, January 24, 2015 7:17 PM

Answers

  • Hello,

    One method is to do this from the database side, create an aliased column as shown below for MS-Access, same can be done with SQL-Server with a slightly different sytnax

    Public Function LoadSomeDataWithAlias() As DataTable
        Dim Builder As New OleDb.OleDbConnectionStringBuilder With
            {
                .Provider = "Microsoft.ACE.OLEDB.12.0",
                .DataSource = IO.Path.Combine(Application.StartupPath, "Database1.accdb")
            }
        Using cn As New OleDb.OleDbConnection With {.ConnectionString = Builder.ConnectionString}
            Using cmd As New OleDb.OleDbCommand With {.Connection = cn}
                cmd.CommandText =
                    <SQL>
                    SELECT 
                        Identifier, 
                        IIf(
                            IsNull(SubQuestion)=True,
                                Question,
                                Trim(Str(Question))+SubQuestion) AS QuestionNumber, 
                        Question, 
                        SubQuestion
                        FROM TABLE2
                        ORDER BY Question, SubQuestion;                                  
                    </SQL>.Value
                Dim dt As New DataTable
    
                Try
                    cn.Open()
    
                    dt.Load(cmd.ExecuteReader)
                    dt.Columns("Identifier").ColumnMapping = MappingType.Hidden
                    'dt.Columns("Question").ColumnMapping = MappingType.Hidden
                    'dt.Columns("SubQuestion").ColumnMapping = MappingType.Hidden
    
    
                Catch ex As Exception
                    MessageBox.Show("Failed to load customer data. See error message below" & Environment.NewLine & ex.Message)
                End Try
    
                Return dt
    
            End Using
        End Using
    End Function

    We have in this case three fields in the select in the event you might need to work with the question or subquestion as I broke them up only to put them back together and the sort order is respected, first the numeric then the string.

    Raw data


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem.

    • Marked as answer by Dominicwild Saturday, January 24, 2015 9:41 PM
    Saturday, January 24, 2015 8:40 PM
  • How would I go about sorting this data?

    When I do the normal "ORDER BY  QuestionNumber ASC" It only considers the first character, then sorts by that. I.e 10 appears at the top of the list, when it should be at the bottom.

    How would I write an SQL statement to sort this correctly?

    Like Blackwood, I don't know how to do that in SQL, but this might give you something to "give" to the SQL statement:

    Option Strict On Option Explicit On Option Infer Off Public Class Form1 Private Sub Form1_Load(ByVal sender As System.Object, _ ByVal e As System.EventArgs) _ Handles MyBase.Load Dim list As New List(Of String) With list .Add("10b") .Add("10c") .Add("10d") .Add("1a") .Add("1b") .Add("2") .Add("3a") .Add("1a") .Add("1b") End With Dim testExcludeDuplicates() As String = _ SpecialStrings.Sort(list) Dim testIncludeDuplicates() As String = _ SpecialStrings.Sort(list, False) Stop End Sub End Class Public NotInheritable Class SpecialStrings Private _numericPortion As Integer Private _alphaPortion As String = "" Private _combined As String Private Sub New(ByVal numeric As Integer, _ ByVal alpha As String) Try If String.IsNullOrEmpty(alpha) OrElse alpha.Trim = "" Then _alphaPortion = "" Else _alphaPortion = alpha End If _numericPortion = numeric _combined = numeric.ToString & alpha.Trim Catch ex As Exception Throw End Try End Sub Public Shared Function Sort(ByVal stringList As List(Of String), _ Optional ByVal excludeDuplicates As Boolean = True) As String() Dim retVal() As String = New String() {} Try If stringList Is Nothing Then Throw New NullReferenceException("The collection of strings cannot be null.") Else Dim ssList As New List(Of SpecialStrings) If stringList.Count > 0 Then For Each s As String In stringList s = s.Trim Dim len As Integer = 0 For Each c As Char In s If IsNumeric(c) Then len += 1 End If Next If s.Length = len Then ssList.Add(New SpecialStrings(CInt(s.Substring(0, len)), "")) Else ssList.Add(New SpecialStrings(CInt(s.Substring(0, len)), s.Substring(len))) End If Next Dim qry As IEnumerable(Of String) = Nothing If excludeDuplicates Then qry = _ From ss As SpecialStrings In ssList _ Order By ss._numericPortion, _ ss._alphaPortion _ Select ss._combined _ Distinct Else qry = _ From ss As SpecialStrings In ssList _ Order By ss._numericPortion, _ ss._alphaPortion _ Select ss._combined End If If qry IsNot Nothing AndAlso qry.Count > 0 Then retVal = qry.ToArray End If End If End If Catch ex As Exception Throw End Try Return retVal End Function End Class


    The "Stop" you see there will work like a breakpoint and the program will halt when it gets to it. If you then hover your mouse over the two variables shown at the end of .Load event, you'll see the values which will be a string array.

    You can see that it will properly sort them with or without excluding the duplicates.

    ***** EDIT *****

    I added in all of the ones from your screenshot (at least I think I did) and the following shows the results:


    Still lost in code, just at a little higher level.

    :-)


    • Edited by Frank L. Smith Saturday, January 24, 2015 8:35 PM ...added screenshots
    • Marked as answer by Dominicwild Saturday, January 24, 2015 9:41 PM
    Saturday, January 24, 2015 8:13 PM

All replies

  • These are strings and not numbers, so they are sorted alphabetically and "10b" does come before "3a" (because "1" comes before "3"). If you want the treat the digits as if they are numbers, you would either need to separate them from the letters, or pad the short strings with leading zeros so that alphabetical sorting gets you the order you want. 

    I'm not sure how to do that in SQL, perhaps you should post in a SQL forum. 

    Saturday, January 24, 2015 7:27 PM
  • How would I go about sorting this data?

    When I do the normal "ORDER BY  QuestionNumber ASC" It only considers the first character, then sorts by that. I.e 10 appears at the top of the list, when it should be at the bottom.

    How would I write an SQL statement to sort this correctly?

    Like Blackwood, I don't know how to do that in SQL, but this might give you something to "give" to the SQL statement:

    Option Strict On Option Explicit On Option Infer Off Public Class Form1 Private Sub Form1_Load(ByVal sender As System.Object, _ ByVal e As System.EventArgs) _ Handles MyBase.Load Dim list As New List(Of String) With list .Add("10b") .Add("10c") .Add("10d") .Add("1a") .Add("1b") .Add("2") .Add("3a") .Add("1a") .Add("1b") End With Dim testExcludeDuplicates() As String = _ SpecialStrings.Sort(list) Dim testIncludeDuplicates() As String = _ SpecialStrings.Sort(list, False) Stop End Sub End Class Public NotInheritable Class SpecialStrings Private _numericPortion As Integer Private _alphaPortion As String = "" Private _combined As String Private Sub New(ByVal numeric As Integer, _ ByVal alpha As String) Try If String.IsNullOrEmpty(alpha) OrElse alpha.Trim = "" Then _alphaPortion = "" Else _alphaPortion = alpha End If _numericPortion = numeric _combined = numeric.ToString & alpha.Trim Catch ex As Exception Throw End Try End Sub Public Shared Function Sort(ByVal stringList As List(Of String), _ Optional ByVal excludeDuplicates As Boolean = True) As String() Dim retVal() As String = New String() {} Try If stringList Is Nothing Then Throw New NullReferenceException("The collection of strings cannot be null.") Else Dim ssList As New List(Of SpecialStrings) If stringList.Count > 0 Then For Each s As String In stringList s = s.Trim Dim len As Integer = 0 For Each c As Char In s If IsNumeric(c) Then len += 1 End If Next If s.Length = len Then ssList.Add(New SpecialStrings(CInt(s.Substring(0, len)), "")) Else ssList.Add(New SpecialStrings(CInt(s.Substring(0, len)), s.Substring(len))) End If Next Dim qry As IEnumerable(Of String) = Nothing If excludeDuplicates Then qry = _ From ss As SpecialStrings In ssList _ Order By ss._numericPortion, _ ss._alphaPortion _ Select ss._combined _ Distinct Else qry = _ From ss As SpecialStrings In ssList _ Order By ss._numericPortion, _ ss._alphaPortion _ Select ss._combined End If If qry IsNot Nothing AndAlso qry.Count > 0 Then retVal = qry.ToArray End If End If End If Catch ex As Exception Throw End Try Return retVal End Function End Class


    The "Stop" you see there will work like a breakpoint and the program will halt when it gets to it. If you then hover your mouse over the two variables shown at the end of .Load event, you'll see the values which will be a string array.

    You can see that it will properly sort them with or without excluding the duplicates.

    ***** EDIT *****

    I added in all of the ones from your screenshot (at least I think I did) and the following shows the results:


    Still lost in code, just at a little higher level.

    :-)


    • Edited by Frank L. Smith Saturday, January 24, 2015 8:35 PM ...added screenshots
    • Marked as answer by Dominicwild Saturday, January 24, 2015 9:41 PM
    Saturday, January 24, 2015 8:13 PM
  • Hello,

    One method is to do this from the database side, create an aliased column as shown below for MS-Access, same can be done with SQL-Server with a slightly different sytnax

    Public Function LoadSomeDataWithAlias() As DataTable
        Dim Builder As New OleDb.OleDbConnectionStringBuilder With
            {
                .Provider = "Microsoft.ACE.OLEDB.12.0",
                .DataSource = IO.Path.Combine(Application.StartupPath, "Database1.accdb")
            }
        Using cn As New OleDb.OleDbConnection With {.ConnectionString = Builder.ConnectionString}
            Using cmd As New OleDb.OleDbCommand With {.Connection = cn}
                cmd.CommandText =
                    <SQL>
                    SELECT 
                        Identifier, 
                        IIf(
                            IsNull(SubQuestion)=True,
                                Question,
                                Trim(Str(Question))+SubQuestion) AS QuestionNumber, 
                        Question, 
                        SubQuestion
                        FROM TABLE2
                        ORDER BY Question, SubQuestion;                                  
                    </SQL>.Value
                Dim dt As New DataTable
    
                Try
                    cn.Open()
    
                    dt.Load(cmd.ExecuteReader)
                    dt.Columns("Identifier").ColumnMapping = MappingType.Hidden
                    'dt.Columns("Question").ColumnMapping = MappingType.Hidden
                    'dt.Columns("SubQuestion").ColumnMapping = MappingType.Hidden
    
    
                Catch ex As Exception
                    MessageBox.Show("Failed to load customer data. See error message below" & Environment.NewLine & ex.Message)
                End Try
    
                Return dt
    
            End Using
        End Using
    End Function

    We have in this case three fields in the select in the event you might need to work with the question or subquestion as I broke them up only to put them back together and the sort order is respected, first the numeric then the string.

    Raw data


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem.

    • Marked as answer by Dominicwild Saturday, January 24, 2015 9:41 PM
    Saturday, January 24, 2015 8:40 PM
  • Kev,

    Good work.

    I don't know the "database" part of it all, but effectively we both did the same thing:

    Strip out the numeric part as an integer so that it could be sorted on by value, then the remainder is the alpha part to be sorted by string sorting rules.


    Still lost in code, just at a little higher level.

    :-)

    Saturday, January 24, 2015 8:49 PM
  • Exactly in regards to sorting a-z with 0-9. I did notice duplicates but feel that should not happen but as it has here, this SQL SELECT placed into my last example would take care of the dups.

    SELECT DISTINCT 
        T.Question, 
        T.SubQuestion
    FROM 
        (
            SELECT 
                Identifier, 
                Question,
                SubQuestion ,  
                IIf(IsNull(SubQuestion)=True,
                    Question,
                    Str(Question)+SubQuestion) AS QuestionNumber    
            FROM TABLE2 
    ORDER BY Question,SubQuestion)  AS T;


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem.

    Saturday, January 24, 2015 9:20 PM
  • Exactly in regards to sorting a-z with 0-9. I did notice duplicates but feel that should not happen but as it has here, this SQL SELECT placed into my last example would take care of the dups.

    SELECT DISTINCT 
        T.Question, 
        T.SubQuestion
    FROM 
        (
            SELECT 
                Identifier, 
                Question,
                SubQuestion ,  
                IIf(IsNull(SubQuestion)=True,
                    Question,
                    Str(Question)+SubQuestion) AS QuestionNumber    
            FROM TABLE2 
    ORDER BY Question,SubQuestion)  AS T;


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem.

    When I first looked at it, I thought it was an oversight that there were duplicates, but the more I looked at it the more obvious it became that clearly it's there for a reason - with 1a five times in succession, that's not a mistake.

    Why that's so, I don't know, but that's also why in mine I set it up as an optional parameter, defaulting to NOT include those duplicates.

    We'll see I guess. :)


    Still lost in code, just at a little higher level.

    :-)

    Saturday, January 24, 2015 9:35 PM
  • One case would be if there were categories either stored flat or in another table related to this table then combining that key would mean no duplicates.

    Example

    VB.NET (foreign key 1) 1A, 1B etc.

    C# (foreign key 2) 1A, 1B etc.

    ASP.NET (foreign key 3) 1A, 1B etc.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem.

    Saturday, January 24, 2015 9:46 PM