Solved

Order by and Select TOP in Datable

Posted on 2013-06-10
21
472 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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 

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 500 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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

Article by: Kraeven
Introduction Remote Share is a simple remote sharing tool, enabling you to see, add and remove remote or local shares. The application is written in VB.NET targeting the .NET framework 2.0. The source code and the compiled programs have been in…
Microsoft Reports are based on a report definition, which is an XML file that describes data and layout for the report, with a different extension. You can create a client-side report definition language (*.rdlc) file with Visual Studio, and build g…
Come and listen to Percona CEO Peter Zaitsev discuss what’s new in Percona open source software, including Percona Server for MySQL (https://www.percona.com/software/mysql-database/percona-server) and MongoDB (https://www.percona.com/software/mongo-…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

707 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