Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
I have a dataset in which there is a column contains various string type values like below:
 
Aircraft
Crime
Package Total
Apartments
DIC - Personnel
 
Now the requirement is that after applying sorting logic on this colum if there is a "Package Total" value in it then it must come at the top position on the Dataset and after that all other values should be in alphabatically sorted order like below:
 
Package Total
Aircraft
Apartments
Crime
DIC - Personnel
 
We have used in Database below logic which is working fine but can't figure it out how to do it on Dataset VB.net from Fronend side:
ORDER BY CASE WHEN UseCarrierAllocation = 0 THEN CASE WHEN InvoiceItemLevel LIKE 'Package Total%' THEN 0 ELSE 1 END END, InvoiceItemLevel ASC
 
Any reply/idea will be helpful!
Posted 27-Nov-13 0:13am
Comments
woopsydoozy at 27-Nov-13 12:20pm
   
what mechanism are you using to apply the sort on the front end? The Sort property of a DataView, I assume? Probably need to add a data column that will do the sort for you. Like add a case column to your query, something like: CASE WHEN InvoiceItemLevel LIKE 'Package Total%' THEN 'AAAAA' ELSE InvoiceItemLevel END AS SORTVAL
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 3

Here are two options to achieve this. The first is purely DataTable manipulations and the second uses "LINQ to DataSet"[^] to create a DataView.
 
Edit: Modified previous example to include using Cache.
Partial Public Class _Default
   Inherits System.Web.UI.Page
 
   Private ds As New DataSet("dsFred")
 
   Private Sub form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles form1.Load
      GetDataSet()
 
      ' Generate the Columns
      AddColumnsToGridView(GridView1, ds.Tables("Fred"))
      AddColumnsToGridView(GridView2, ds.Tables("Fred"))
 
      GridView1.DataSource = Option1(ds.Tables("Fred"))
      GridView1.DataBind()
 
      GridView2.DataSource = Option2(ds.Tables("Fred"))
      GridView2.DataBind()
   End Sub
 
   Private Sub form1_Unload(ByVal sender As Object, ByVal e As System.EventArgs) Handles form1.Unload
      If Cache("dsFred") Is Nothing Then Cache("dsFred") = ds
   End Sub
 
   Private Function Option1(ByVal dt As DataTable) As DataView
 
      If dt.Columns("PrimarySort") Is Nothing Then
         ' add a computed column to the DataTable
         ' it will set the column value to 0(zero) if the pattern matches, else 1
         dt.Columns.Add("PrimarySort", GetType(Byte), "iif([sourcecolumnname] Like 'Package Total%', 0, 1)")
      End If
 
      Dim dv As New DataView(dt)
      ' set the sort to sort first on PrimarySort then original source column
      dv.Sort = "[PrimarySort] Asc, [sourcecolumnname] Asc"
 
      Return dv
   End Function
 
   Private Function Option2(ByVal dt As DataTable) As DataView
      ' This use Linq To DataSet
      ' Make sure that you have a project reference for:  System.Data.DataSetExtensions

      ' 2 ways to define the query

      Dim sort As System.Data.EnumerableRowCollection(Of DataRow) = _
         dt.AsEnumerable(). _
         OrderBy(Function(r As DataRow) IIf(r.Field(Of String)("sourcecolumnname") Like "Package Total*", 0, 1)). _
         ThenBy(Function(r As DataRow) r.Field(Of String)("sourcecolumnname"))
 
      ' this query may be a little bit easier to read
      'Dim sort As System.Data.EnumerableRowCollection(Of DataRow) = _
      '  From r In dt.AsEnumerable _
      '  Order By IIf(r.Field(Of String)("sourcecolumnname") Like "Package Total*", 0, 1), r.Field(Of String)("sourcecolumnname") _
      '  Select r

      Return sort.AsDataView()
   End Function
 
   Private Sub GetDataSet()
      If Cache("dsFred") Is Nothing Then
         Fill(ds)
      Else
         ds = CType(Cache("dsFred"), DataSet)
      End If
   End Sub
 
   Private Sub Fill(ByRef ds As DataSet)
      ' used to simulate filling the table from a query
      Dim dt As New DataTable
      With dt
         dt.TableName = "Fred"
         .Columns.Add("sourcecolumnname", GetType(String))
         .Rows.Add(New Object() {"Package Total 2"})
         .Rows.Add(New Object() {"Crime"})
         .Rows.Add(New Object() {"Apartments"})
         .Rows.Add(New Object() {"DIC - Personnel"})
         .Rows.Add(New Object() {"Package Total"})
      End With
      ds.Tables.Add(dt)
   End Sub
 
   Private Sub AddColumnsToGridView(ByVal gv As GridView, ByVal dt As DataTable)
   ' Ref - Answer from Steve Hibbert: http://stackoverflow.com/questions/2091457/how-to-hide-columns-in-an-asp-net-gridview-with-auto-generated-columns
      gv.Columns.Clear()
      For Each col As DataColumn In dt.Columns
         Dim field As New BoundField
         field.DataField = col.ColumnName
         field.HeaderText = col.ColumnName
         gv.Columns.Add(field)
      Next
   End Sub
End Class
  Permalink  
v3
Comments
Goel Himanshu at 29-Nov-13 5:59am
   
Hi it helps me a lot but there is a another issue after using your first option which is that i get this dataset from a cache then after applying sorting on defaultview it makes it sort for default view only. I want that it should be applied directly on the dataset so that after this step i can just update this dataset in cache so that further on i just retrive that dataset from cache where ever i want which gives me data in the tables in sorted order.
TnTinMn at 29-Nov-13 10:55am
   
I have revised the code to include using the Cache. Be advised that this is my first attempt at a WebForm any more complex than "Hello World", so I may not be handling this correctly. :)
 
I see no need to store a sorted version of the dataset unless it is a performance issue with a WebForm. If that is the case, you could replace the DataTable with the sorted version by creating a table from the DataView (DataView.ToTable()), delete the existing table from the DataSet, and finally adding the new Table to the DataSet.
 
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

Use a primary and secondary sort field. Primary controlled by "Package" and the secondary by the alphabetic order of the other values.
 

insert into sortTest (name) values('Package')
insert into sortTest (name) values('Crime')
insert into sortTest (name) values('DIC')
insert into sortTest (name) values('Aircraft')
insert into sortTest (name) values('Apartments')
 
select 
  name,
  case when name like 'Package%' then 0 else 1 end as PrimarySort,
  case when name like 'Package%' then null else name end as SecondarySort
 from sortTest
order by PrimarySort Asc, SecondarySort Asc
 
name       PrimarySort SecondarySort
---------- ----------- -------------
Package    0           NULL
Aircraft   1           Aircraft
Apartments 1           Apartments
Crime      1           Crime
DIC        1           DIC
 
If there are multiple values starting with "Package" and you want ordering within them then the secondary sort selection becomes:
 
select
  name,
  case when name like 'Package%' then 0 else 1 end as PrimarySort,
  name as SecondarySort
 from sortTest
order by PrimarySort Asc, SecondarySort Asc
 
name       PrimarySort SecondarySort
---------- ----------- -------------
Package    0           Package
Package A  0           Package A
Package B  0           Package B
Aircraft   1           Aircraft
Apartments 1           Apartments
Crime      1           Crime
DIC        1           DIC
Of course you don't need a separate SecondarySort column now as it's just name, but using it makes your intentions clear and it means if your sort rules change you can change server side code with minimal (possibly no) disruption to client code.
 
Throw away the package value(s) and re-run the select.
 
delete from sortTest where name like 'Package%'
 
select 
  name,
  case when name like 'Package%' then 0 else 1 end as PrimarySort,
  name as SecondarySort
 from sortTest
order by PrimarySort Asc, SecondarySort Asc
 
name       PrimarySort SecondarySort
---------- ----------- -------------
Aircraft   1           Aircraft
Apartments 1           Apartments
Crime      1           Crime
DIC        1           DIC
 
 
And in your VB if you use a dataview to feed the output rather than the dataset and you want to make doubly sure that this order is preserved..
 
Dim dv as DataView = New DataView(myDataSet.Tables("myTable"))
dv.Sort = "PrimarySort Asc, SecondarySort Asc"
 
...but I don't think that'll be necessary unless you are allowing users to remove rows from the datatable client-side.
  Permalink  
v3
Comments
Goel Himanshu at 28-Nov-13 6:14am
   
Thanks for your reply... the solution which you told me will not work as i have apply both condition only on one column. I have this dataset table with various columns and one of the column is "InvoiceItemLevel" which i have mentioned with various values. So i need to sort that dataset table using multiple condition like i have said i need package line first then all in alphabetical order.
I have used below logic but it is for single alphabetical sorting condition but i also had to add a condition for "Pacakage total" value and if this value is exist in dataset table this row must be moved to first position of the dataset.
DataSet.Tables[0].DefaultView.Sort = "InvoiceItemLevel asc";
cigwork at 28-Nov-13 14:27pm
   
If you can't you use two (or more if necessary) sort columns can you concatenate values?
Something like ...
 
select
name,
case when name like 'PackageTotal' then '00' else '01' + name end as CombinedSort
from sortTest
order by CombinedSort
 
myDataView.Sort = "CombinedSort ASC"
 
If you can't add columns to the result set then perhaps return a single InvoiceItemLevel with a prefix as shown for the CombinedSort above and then (oh this is horrible) use a template column rather than a "plain text" column in your datagrid to display the invoiceitemlevel without the sort prefix
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

If i understand you well, you have got 2 columns in a result set, for example:
NameOfSomething  Total
Package Total    500
Aircraft         300
Apartments       250
Crime            200
DIC - Personnel  150
 
If it is a true, you should sort data on the second column.
SELECT NameOfSomething, Total
FROM ( 
    SELECT NameOfSomething, SUM(CountOfName) AS Total
    FROM TableName
    GROUP BY NameOfSomething
     ) AS T
ORDER BY Total DESC, NameOfSomething ASC
 
For further information, please see: ORDER BY clause (T-SQL)[^]
 
Of course, you can still use CASE stetement with ORDER clause.
SELECT ...
FROM ...
ORDER BY CASE WHEN NameOfSomething Like '%Total' THEN 0 ELSE 1 END ASC, OtherField DESC
 
More about:
CASE (T-SQL) statement[^]
SQL Server – Custom sorting in ORDER BY clause[^]
  Permalink  
Comments
Goel Himanshu at 28-Nov-13 6:12am
   
Thanks for your reply... the solution which you told me is from Database side which i have already implemented and written in my question. The problem is that i have to do it from Frontend side means using vb.net. I have this dataset table with various columns and one of the column is "InvoiceItemLevel" which i have mentioned with various values. So i need to sort that dataset table using multiple condition like i have said i need package line first then all in alphabetical order.
I have used below logic but it is for single alphabetical sorting condition but i also had to add a condition for "Pacakage total" value and if this value is exist in dataset table this row must be moved to first position of the dataset.
DataSet.Tables[0].DefaultView.Sort = "InvoiceItemLevel asc";

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 George Jonsson 175
1 Kornfeld Eliyahu Peter 169
2 Zoltán Zörgő 139
3 PIEBALDconsult 130
4 OriginalGriff 120
0 OriginalGriff 6,165
1 DamithSL 4,658
2 Maciej Los 4,107
3 Kornfeld Eliyahu Peter 3,649
4 Sergey Alexandrovich Kryukov 3,342


Advertise | Privacy | Mobile
Web04 | 2.8.141220.1 | Last Updated 29 Nov 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100