[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Order by and Select TOP in Datable

Posted on 2013-06-10
21
Medium Priority
?
512 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:Meir Rivkin
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:Meir Rivkin
ID: 39234377
or using linq:
datatable.Rows.OrderByDescending(n=>n["MODIFIED-DATE"]).FirstOrDefault();

Open in new window

0
 
LVL 42

Expert Comment

by:Meir Rivkin
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

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:Meir Rivkin
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:Meir Rivkin
ID: 39234473
what error?
0
 

Author Comment

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

Expert Comment

by:Meir Rivkin
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:Meir Rivkin
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:Meir Rivkin
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:Meir Rivkin
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:Meir Rivkin
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:Meir Rivkin
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:
Meir Rivkin 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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
Suggested Courses

650 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