Sorting in datatable

Question

I have a list of records in database class

D1.1: First
D2.12: second
D1.14: third
D2.8: fourth
D2.2: fifth
D2.3: fifth
D1.7: fifth
D2: fifth
D1.5: fifth

How should I sort by taking the consideration the codes of D like 1.1, 2.12. 1.14 etc ?

The output required should be below.

D1.1: First  
D1.14: third  
D1.5: fifth
D1.7: fifth    
D2: fifth
D2.12: second
D2.2: fifth
D2.3: fifth
D2.8: fourth

Why is D1.14 after D1.5 but D2.12 comes before D2.2 ?? Once (for D1) you seem to be sorting numerically, the other time (for D2) you seem to be sorting string-like ..... what do you really want??? — marc_s
– marc_s, Commented Jun 23, 2011 at 8:26

Ira Rainey · Accepted Answer · 2011-06-23 08:08:00Z

1

Not clear exactly from your post how the data is in the actual table, but if it's in the field formatted like that then you could order it by using SUBSTRING. Here's an example:

DECLARE @test AS TABLE (
data varchar(50)
)

INSERT @test VALUES ('D1.1: First')
INSERT @test VALUES ('D2.12: second')
INSERT @test VALUES ('D1.14: third')
INSERT @test VALUES ('D2.8: fourth')
INSERT @test VALUES ('D2.2: fifth')
INSERT @test VALUES ('D2.3: fifth')
INSERT @test VALUES ('D1.7: fifth')
INSERT @test VALUES ('D2: fifth')
INSERT @test VALUES ('D1.5: fifth')

SELECT data FROM @test
ORDER BY SUBSTRING(data,2,CHARINDEX(':',data,0)-2)

Result:

data
--------------------------------------------------
D1.1: First
D1.14: third
D1.5: fifth
D1.7: fifth
D2: fifth
D2.12: second
D2.2: fifth
D2.3: fifth
D2.8: fourth

answered Jun 23, 2011 at 8:08

Ira Rainey

5,2152 gold badges36 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

14 Comments

Ira Rainey Over a year ago

Downvote if you feel it's not helpful. I did read the question and understand that you're talking about a datatable in C#, but my point is that from your original question it's not clear how the data is held or originally structured. My approach to the information I had was to order the data in SQL thus populating the datatable with the data in the correct format in the first place. If the datatable isn't populated from a SQL statement then obviously this is no good for you.

Pankaj Over a year ago

No, we have records at front end only. Thus, it does not make sense to go in database. That's why I did not include other tags!! I need to do it in front end only.

Pankaj Over a year ago

Sorry, I am not interested in this suggestion.

Ira Rainey Over a year ago

Fair enough. Take a look at this instead then: stackoverflow.com/questions/2294180/sort-items-in-datatable

Pankaj Over a year ago

It is not taking into consideration for numeric values. Not interested in this approach. It will create bugs.

|

Collectives™ on Stack Overflow

Sorting in datatable

1 Answer 1

14 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

14 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related