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

How do you sort a datatable

I  query a database and store the results in a datatable called dtcertificates.
There are several columns but for simplicity a date column and a value column.
I want to sort dtcertificates via value desc then move dtcertificates to a datatable called worktable1. Then I want to sort dtcertificates via date desc then move dtcertificates to a datatable called worktable2
Can anyone help me with this?
Thanks
0
whiwex
Asked:
whiwex
1 Solution
 
lucky_jamesCommented:
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you don't sort a datatable, but you sort a dataview...
should be straightforward with the DefaultView of the Datatable, and use the Sort property
0
 
whiwexAuthor Commented:
I figured it out Thanks
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
Joseph HornseyPresident and JanitorCommented:
Can you post some of the source code?

Why don't you try using views?  A view, simply put, is a filtered query on a table (or tables).

So, let's say your base table is "table1".  Create two views; one for sorting by value and one for sorting by date:

CREATE VIEW vTable1Date
AS
SELECT * FROM table1 SORT BY date DESC

CREATE VIEW vTable1Value
AS
SELECT * FROM table1 SORT BY value DESC

"SELECT * FROM vTable1Date" will always produce a result set which includes everything in table1 sorted by date, so you can query the views to get your result sets to populate the other two tables.

CREATE TABLE newTableDate (ColumnA, ColumnB, ColumnC)  <----Whatever columns you need in this table
INSERT INTO newTableDate SELECT * FROM vTable1Date

The syntax above isn't exact, but it should give you an idea of where to start.  Keep in mind that if you wish, the view can include only specific columns from each of the base tables the view includes.  So, you should be able to match the view up to what you want the new tables to look like.

<-=+=->
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
just a short comment about the "VIEW + ORDER BY":
first of all, if you are using MsSQL, the syntax is incomplete, and depending on the version, it would not even return data correctly (requires some sp...)

finally, with the dataview having the sort method which is fast, you should not need to use a view's sort method, as this can influence the execution plan (for the worsts)

CREATE VIEW vTable1Value
AS
SELECT TOP 100 PERCENT * FROM table1 SORT BY value DESC

Open in new window

0
 
AskRickCommented:
Let me know if I understand your requirement correctly.  From your example, I am thinking you are wanting to Q1) create workables from Q2) sorted query result sets.

What is the reasons doing this?  It seems to me that you are trying to perform vertical data partitioning.  I am guessing you want to create lookup tables for data load performance improvement reasons.  

Anyway, quick answer to your question would be:

It is easy to accomplish Q1, you can either
 - create the worktable in dynamically (SELECT ... INTO worktable1 FROM datatable ORDER BY ... DESC)
 - or create the table first

Regarding Q2, if you drop and create a the worktable everytime, it is very likely that the newly created work table is sort exactly as the query result set.  However, best practice would be create the worktable first with the clustered index on the sort columns.

Cheers,

Rick
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

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