?
Solved

Order by and Select TOP in Datable

Posted on 2013-06-10
21
Medium Priority
?
494 Views
Last Modified: 2013-06-11
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
Comment
Question by:GRChandrashekar
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 12
  • 9
21 Comments
 
LVL 42

Expert Comment

by:sedgwick
ID: 39234374
DataView dv = datatable.DefaultView;
   dv.Sort = "MODIFIED-DATE desc";
DataRowView drv = dv[0];

Open in new window

0
 
LVL 42

Expert Comment

by:sedgwick
ID: 39234377
or using linq:
datatable.Rows.OrderByDescending(n=>n["MODIFIED-DATE"]).FirstOrDefault();

Open in new window

0
 
LVL 42

Expert Comment

by:sedgwick
ID: 39234381
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:GRChandrashekar
ID: 39234395
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
 
LVL 42

Expert Comment

by:sedgwick
ID: 39234454
 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
 

Author Comment

by:GRChandrashekar
ID: 39234466
tableOrder.Add(_dview(0).Row)

.add shows error.
0
 
LVL 42

Expert Comment

by:sedgwick
ID: 39234473
what error?
0
 

Author Comment

by:GRChandrashekar
ID: 39234481
.add syntax is wrong
0
 
LVL 42

Expert Comment

by:sedgwick
ID: 39234486
change it to:
tableOrder.Rows.Add(_dview(0).Row.ItemArray)

Open in new window

0
 

Author Comment

by:GRChandrashekar
ID: 39234511
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
 
LVL 42

Expert Comment

by:sedgwick
ID: 39234519
in line 6, do u get the top 1 row according to sorting criteria?
0
 

Author Comment

by:GRChandrashekar
ID: 39234525
No
0
 
LVL 42

Expert Comment

by:sedgwick
ID: 39234528
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
 

Author Comment

by:GRChandrashekar
ID: 39234551
removed line 6
what change to make in line 8
tableOrder.Rows.Add(_dview(0).Row.ItemArray)
0
 
LVL 42

Expert Comment

by:sedgwick
ID: 39234564
nothing really, _dview should be updated after setting the .Sort property.
can u make sure that ModifiedDate field is of type DateTime?
0
 
LVL 42

Expert Comment

by:sedgwick
ID: 39234627
have u managed to run this?
0
 

Author Comment

by:GRChandrashekar
ID: 39234644
Tried but didnt work though line 5 sorts correctly in DESC order
0
 
LVL 42

Expert Comment

by:sedgwick
ID: 39234723
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
 

Author Comment

by:GRChandrashekar
ID: 39234751
When it comes to this  Dim tableOrder = DataTableCloneClass.ClonedDataTable(dt)

sorting goes off
0
 

Author Comment

by:GRChandrashekar
ID: 39237044
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
 
LVL 42

Accepted Solution

by:
sedgwick earned 2000 total points
ID: 39237060
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

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!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Since .Net 2.0, Visual Basic has made it easy to create a splash screen and set it via the "Splash Screen" drop down in the Project Properties.  A splash screen set in this manner is automatically created, displayed and closed by the framework itsel…
Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question