• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 529
  • Last Modified:

Order by and Select TOP in Datable

Hi, I have a datatable. I need to sort this by MODIFIED-DATE DESC and return top 1 row in output. How do I do this ?
0
GRChandrashekar
Asked:
GRChandrashekar
  • 12
  • 9
1 Solution
 
Meir RivkinFull stack Software EngineerCommented:
DataView dv = datatable.DefaultView;
   dv.Sort = "MODIFIED-DATE desc";
DataRowView drv = dv[0];

Open in new window

0
 
Meir RivkinFull stack Software EngineerCommented:
or using linq:
datatable.Rows.OrderByDescending(n=>n["MODIFIED-DATE"]).FirstOrDefault();

Open in new window

0
 
Meir RivkinFull stack Software EngineerCommented:
sorry didn't see it's vb.net:
dmi dv as DataView = datatable.DefaultView
   dv.Sort = "MODIFIED-DATE desc"
dim drv as DataRowView = dv(0)

Open in new window


and linq:
datatable.Rows.OrderByDescending(function(n) n["MODIFIED-DATE"]).FirstOrDefault()

Open in new window

0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
GRChandrashekarAuthor Commented:
How to return the output in data table ?
 Public Shared Function Lastmodifiedrecord(dt As DataTable) As DataTable
        Try
            Dim datatable = DataTableCloneClass.ClonedDataTable(dt)
            _dview = Nothing
            _dview = datatable.DefaultView
            _dview.Sort = "MODIFIEDDATE DESC"
            Dim drv As DataRowView = _dview(0)
             -----------?

        Catch ex As Exception
            ErrorHandlerClass.LogMessage(ex.Message + ex.StackTrace)
            Throw
        End Try
    End Function

Open in new window

0
 
Meir RivkinFull stack Software EngineerCommented:
 Public Shared Function Lastmodifiedrecord(dt As DataTable) As DataTable
        Try
            Dim datatable = DataTableCloneClass.ClonedDataTable(dt)
            _dview = Nothing
            _dview = datatable.DefaultView
            _dview.Sort = "MODIFIEDDATE DESC"
dim tableOrder = DataTableCloneClass.ClonedDataTable(dt)
tableOrder.Add(_dview(0).Row)
return tableOrder

        Catch ex As Exception
            ErrorHandlerClass.LogMessage(ex.Message + ex.StackTrace)
            Throw
        End Try
    End Function

Open in new window

0
 
GRChandrashekarAuthor Commented:
tableOrder.Add(_dview(0).Row)

.add shows error.
0
 
Meir RivkinFull stack Software EngineerCommented:
what error?
0
 
GRChandrashekarAuthor Commented:
.add syntax is wrong
0
 
Meir RivkinFull stack Software EngineerCommented:
change it to:
tableOrder.Rows.Add(_dview(0).Row.ItemArray)

Open in new window

0
 
GRChandrashekarAuthor Commented:
Public Shared Function Lastmodifiedrecord(dt As DataTable) As DataTable
        Try
            _dview = Nothing
            _dview = DataTableCloneClass.ClonedDataTable(dt).DefaultView
          [b]  _dview.Sort = "MODIFIEDDATE DESC"[/b]
            Dim drv As DataRowView = _dview(0)
            Dim tableOrder = DataTableCloneClass.ClonedDataTable(dt)
            tableOrder.Rows.Add(_dview(0).Row.ItemArray)
            Return tableOrder
        Catch ex As Exception
            ErrorHandlerClass.LogMessage(ex.Message + ex.StackTrace)
            Throw
        End Try
    End Function

Open in new window


Till the high lighted line it is fine. After that it is not sorted and returning all records
0
 
Meir RivkinFull stack Software EngineerCommented:
in line 6, do u get the top 1 row according to sorting criteria?
0
 
GRChandrashekarAuthor Commented:
No
0
 
Meir RivkinFull stack Software EngineerCommented:
line 6 is actually redundant, u can remove it from your code.
on line 8 i get the top 1 row after the sorting took place.
0
 
GRChandrashekarAuthor Commented:
removed line 6
what change to make in line 8
tableOrder.Rows.Add(_dview(0).Row.ItemArray)
0
 
Meir RivkinFull stack Software EngineerCommented:
nothing really, _dview should be updated after setting the .Sort property.
can u make sure that ModifiedDate field is of type DateTime?
0
 
Meir RivkinFull stack Software EngineerCommented:
have u managed to run this?
0
 
GRChandrashekarAuthor Commented:
Tried but didnt work though line 5 sorts correctly in DESC order
0
 
Meir RivkinFull stack Software EngineerCommented:
so if the sorting is working properly, then check _dview(0).Row which is the first row after the sorting took place, which means the top 1.
0
 
GRChandrashekarAuthor Commented:
When it comes to this  Dim tableOrder = DataTableCloneClass.ClonedDataTable(dt)

sorting goes off
0
 
GRChandrashekarAuthor Commented:
Tried with this but no luck again

 _dview = Nothing
            _dview = DataTableCloneClass.ClonedDataTable(dt).DefaultView
            _dview.Sort = "MODIFIEDDATE DESC"
            _dview.Table.AsEnumerable().Take(1)
            Return _dview

Open in new window

0
 
Meir RivkinFull stack Software EngineerCommented:
cloning datatable shouldn't affect the sorting of the original view.
try this:
Public Shared Function Lastmodifiedrecord(dt As DataTable) As DataTable
        Try
            dt.DefaultView.Sort = "MODIFIEDDATE DESC"
dim tableOrder as DataTable
tableOrder = dt.Clone()
        tableOrder.Rows.Add(dt.DefaultView.Item(0).Row.ItemArray)
            Return tableOrder
        Catch ex As Exception
            ErrorHandlerClass.LogMessage(ex.Message + ex.StackTrace)
            Throw
        End Try
    End Function

Open in new window

0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 12
  • 9
Tackle projects and never again get stuck behind a technical roadblock.
Join Now