Sorting this data - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Sorting this data

Author

Topic

pareshmotiwala
Constraint Violating Yak Guru

323 Posts

Posted - 2007-06-25 : 15:49:55

Hi
I have a whole bunch of values like

423.0
150.00
300.00
10.0
6.0
2.0
61.0
62.0
63.0
309.00
520.00
2.0
433.00
8.0
26.0
36.0
43.0
6.0
18.0
3.0
7.0
12.0
17.0
17.0
2.0
4.0
7.0
70.0
71.0
1.0
2.0
31.0
2.0
Appendix
150.00
520.00
502.00
408.000
520.00
17.0
18.0
3.00
410.00
8.0
305.000
12.0
2.0
6.00
300.000
8.0
9.0
26.0
34.0
36.0
13.00
363.000
364.000
365.000
366.000
5.00
7.00
41.00
7.0
7.00
27.0
11.00
29.00
450.00
520.00
5.0
433.00
130.00
170.00
10.0
4.00
45.00
30.0
366.000
31.0
4.00
410.00
453.00
23.00
9.0
9.0
101.00
28.00
361.000
366.000
505.00
4.00
3.00
36.00
3.0
15.00
505.00
2.0
2.07(24
31.00
128.00
4.00
5.00
6.00
31.0
2.00
34.00
36.00
8.00
9.00
26.00
1.00
2.00
31.00
Appendix M
14.00
10.00
11.00
5.00
41.00
66.00
2.00
63.00
17.00
1.0
100.00
51.00
52.00
53.00
54.00
55.00
56.00
57.00
58.00
59.00
60.00
61.00
62.00
63.0
64.00
65.00
66.00
67.00
68.00
69.00
70.00
71.00
72.00
73.00
74.00
75.00
76.00
364.00
77.00
78.00
79.00
80.00
81.00
82.00
83.00
84.00
85.00
86.00
87.00
88.00
89.00
90.00
93.00
7.00
110.00
120.0
91.00
92.0
50.00
6.00
220.000
221.000
62C.00
3.00
1.00
63.38.
520.000
4.00
25.00
28.00
33.00
37.00
38.00
3.00
3.00
24.00
26.00
4.00
7.00
8.00
2.00
3.00
4.00
5.00
6.00
7.00
8.00
9.00
16.00
1.00
9.00
Appendix H
12.00
410.000
420.000
406.000
433.000
424.000
50.00
2.0
10.00
31.00
4.00
5.00
25.00
401.00
119.00
34.00
37.00
23.00
520.000
519.000
3.00
4.00
22.00
4.00
450.000
505.000
506.000
520.000
6.00
10.00
3.00
4.00
5.00
6.00
30.000
2.00
7.00
309.00
204.00
309.00
364.00
309.00
4.00
140.0
17.0
18.0

Customer wants this to be sorted in order of number.
The original field is varchar?
Anybody have the slightest ideas how I would do that.

Regards
Paresh Motiwala
Boston, USA

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-06-25 : 16:00:27

Like this?

-- prepare sample data
declare	@sample table (data varchar(20))

insert	@sample
select	'423.0' union all
select	'150.00' union all
select	'300.00' union all
select	'10.0' union all
select	'6.0' union all
select	'2.0' union all
select	'61.0' union all
select	'62.0' union all
select	'63.0' union all
select	'309.00' union all
select	'520.00' union all
select	'2.0' union all
select	'433.00' union all
select	'8.0' union all
select	'26.0' union all
select	'36.0' union all
select	'43.0' union all
select	'6.0' union all
select	'18.0' union all
select	'3.0' union all
select	'7.0' union all
select	'12.0' union all
select	'17.0' union all
select	'17.0' union all
select	'2.0' union all
select	'4.0' union all
select	'7.0' union all
select	'70.0' union all
select	'71.0' union all
select	'1.0' union all
select	'2.0' union all
select	'31.0' union all
select	'2.0' union all
select	'Appendix' union all
select	'150.00' union all
select	'520.00' union all
select	'502.00' union all
select	'408.000' union all
select	'520.00' union all
select	'17.0' union all
select	'18.0' union all
select	'3.00' union all
select	'410.00' union all
select	'8.0' union all
select	'305.000' union all
select	'12.0' union all
select	'2.0' union all
select	'6.00' union all
select	'300.000' union all
select	'8.0' union all
select	'9.0' union all
select	'26.0' union all
select	'34.0' union all
select	'36.0' union all
select	'13.00' union all
select	'363.000' union all
select	'364.000' union all
select	'365.000' union all
select	'366.000' union all
select	'5.00' union all
select	'7.00' union all
select	'41.00' union all
select	'7.0' union all
select	'7.00' union all
select	'27.0' union all
select	'11.00' union all
select	'29.00' union all
select	'450.00' union all
select	'520.00' union all
select	'5.0' union all
select	'433.00' union all
select	'130.00' union all
select	'170.00' union all
select	'10.0' union all
select	'4.00' union all
select	'45.00' union all
select	'30.0' union all
select	'366.000' union all
select	'31.0' union all
select	'4.00' union all
select	'410.00' union all
select	'453.00' union all
select	'23.00' union all
select	'9.0' union all
select	'9.0' union all
select	'101.00' union all
select	'28.00' union all
select	'361.000' union all
select	'366.000' union all
select	'505.00' union all
select	'4.00' union all
select	'3.00' union all
select	'36.00' union all
select	'3.0' union all
select	'15.00' union all
select	'505.00' union all
select	'2.0' union all
select	'2.07(24' union all
select	'31.00' union all
select	'128.00' union all
select	'4.00' union all
select	'5.00' union all
select	'6.00' union all
select	'31.0' union all
select	'2.00' union all
select	'34.00' union all
select	'36.00' union all
select	'8.00' union all
select	'9.00' union all
select	'26.00' union all
select	'1.00' union all
select	'2.00' union all
select	'31.00' union all
select	'Appendix M' union all
select	'14.00' union all
select	'10.00' union all
select	'11.00' union all
select	'5.00' union all
select	'41.00' union all
select	'66.00' union all
select	'2.00' union all
select	'63.00' union all
select	'17.00' union all
select	'1.0' union all
select	'100.00' union all
select	'51.00' union all
select	'52.00' union all
select	'53.00' union all
select	'54.00' union all
select	'55.00' union all
select	'56.00' union all
select	'57.00' union all
select	'58.00' union all
select	'59.00' union all
select	'60.00' union all
select	'61.00' union all
select	'62.00' union all
select	'63.0' union all
select	'64.00' union all
select	'65.00' union all
select	'66.00' union all
select	'67.00' union all
select	'68.00' union all
select	'69.00' union all
select	'70.00' union all
select	'71.00' union all
select	'72.00' union all
select	'73.00' union all
select	'74.00' union all
select	'75.00' union all
select	'76.00' union all
select	'364.00' union all
select	'77.00' union all
select	'78.00' union all
select	'79.00' union all
select	'80.00' union all
select	'81.00' union all
select	'82.00' union all
select	'83.00' union all
select	'84.00' union all
select	'85.00' union all
select	'86.00' union all
select	'87.00' union all
select	'88.00' union all
select	'89.00' union all
select	'90.00' union all
select	'93.00' union all
select	'7.00' union all
select	'110.00' union all
select	'120.0' union all
select	'91.00' union all
select	'92.0' union all
select	'50.00' union all
select	'6.00' union all
select	'220.000' union all
select	'221.000' union all
select	'62C.00' union all
select	'3.00' union all
select	'1.00' union all
select	'63.38.' union all
select	'520.000' union all
select	'4.00' union all
select	'25.00' union all
select	'28.00' union all
select	'33.00' union all
select	'37.00' union all
select	'38.00' union all
select	'3.00' union all
select	'3.00' union all
select	'24.00' union all
select	'26.00' union all
select	'4.00' union all
select	'7.00' union all
select	'8.00' union all
select	'2.00' union all
select	'3.00' union all
select	'4.00' union all
select	'5.00' union all
select	'6.00' union all
select	'7.00' union all
select	'8.00' union all
select	'9.00' union all
select	'16.00' union all
select	'1.00' union all
select	'9.00' union all
select	'Appendix H' union all
select	'12.00' union all
select	'410.000' union all
select	'420.000' union all
select	'406.000' union all
select	'433.000' union all
select	'424.000' union all
select	'50.00' union all
select	'2.0' union all
select	'10.00' union all
select	'31.00' union all
select	'4.00' union all
select	'5.00' union all
select	'25.00' union all
select	'401.00' union all
select	'119.00' union all
select	'34.00' union all
select	'37.00' union all
select	'23.00' union all
select	'520.000' union all
select	'519.000' union all
select	'3.00' union all
select	'4.00' union all
select	'22.00' union all
select	'4.00' union all
select	'450.000' union all
select	'505.000' union all
select	'506.000' union all
select	'520.000' union all
select	'6.00' union all
select	'10.00' union all
select	'3.00' union all
select	'4.00' union all
select	'5.00' union all
select	'6.00' union all
select	'30.000' union all
select	'2.00' union all
select	'7.00' union all
select	'309.00' union all
select	'204.00' union all
select	'309.00' union all
select	'364.00' union all
select	'309.00' union all
select	'4.00' union all
select	'140.0' union all
select	'17.0' union all
select	'18.0'

-- show the expected output
select		data
from		@sample
order by	isnumeric(data) desc,
		case when isnumeric(data) = 1 then cast(data as money) end,
		data

Peter Larsson
Helsingborg, Sweden

PSamsig
Constraint Violating Yak Guru

384 Posts

Posted - 2007-06-25 : 17:01:57

The real question is how the customer wants the close-to-be-numbers sorted (i.e. '2.07(24', '62C.00')

-- If you give someone a program, you will frustrate them for a day; if you teach them how to program, you will frustrate them for a lifetime.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-26 : 01:32:45

Isnumric is not reliable
http://aspfaq.com/show.asp?id=2390

Madhivanan

Failing to plan is Planning to fail

pareshmotiwala
Constraint Violating Yak Guru

323 Posts

Posted - 2007-06-26 : 10:32:40

peso's query seems to work. But just one more addition to the facts.
the values are file numbers gotten from a table 'x'

Regards
Paresh Motiwala
Boston, USA

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-06-26 : 13:08:10

[code]select data
from x
order by isnumeric(data) desc,
case when isnumeric(data) = 1 then cast(data as money) end,
data[/code]

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources