Solved

Order by and Select TOP in Datable

Posted on 2013-06-10
21
429 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
  • 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
 

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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

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…
Introduction When many people think of the WebBrowser (http://msdn.microsoft.com/en-us/library/2te2y1x6%28v=VS.85%29.aspx) control, they immediately think of a control which allows the viewing and navigation of web pages. While this is true, it's a…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

760 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now