Sorting 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 data

Author

Topic

lols
Posting Yak Master

174 Posts

Posted - 2008-04-17 : 07:48:30

Hi,

I have a column containing data

3 KB
16 KB
2 KB
4 KB
43 KB

How to sort this column and display results like:

43
16
4
3
2

Thanks.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-17 : 07:49:24

select col1, replace(col1, ' kb', '')
from table1

E 12°55'05.25"
N 56°04'39.16"

lols
Posting Yak Master

174 Posts

Posted - 2008-04-17 : 07:54:52

Hi,

Thanks. My Column containing that data is a varchar(20). How do I sort it? It is not giving correct results.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-17 : 07:56:12

select *
from table1
order by len(col1), col1

E 12°55'05.25"
N 56°04'39.16"

lols
Posting Yak Master

174 Posts

Posted - 2008-04-17 : 07:56:58

I did this:

SELECT col1, replace(col1, ' kb', '') as newcol
from Size order by newcol desc

but results are like this:


120 KB	120
144 KB	144
16 KB	16
16 KB	16
16 KB	16
16 KB	16
16 KB	16
16 KB	16
264 KB	264
288 KB	288
32 KB	32
32 KB	32
336 KB	336
456 KB	456
64 KB	64
8 KB	8
8 KB	8
8 KB	8
8 KB	8
8 KB	8
8 KB	8
8 KB	8
8 KB	8
8 KB	8
8 KB	8
8 KB	8
8 KB	8
8 KB	8
8 KB	8
8 KB	8
8 KB	8
8 KB	8
8 KB	8
8 KB	8
8 KB	8
8 KB	8
8 KB	8
8 KB	8
8 KB	8
8 KB	8
8 KB	8
8 KB	8
8 KB	8
8 KB	8
8 KB	8
8 KB	8
8 KB	8
8 KB	8
8 KB	8
8 KB	8
8 KB	8
8 KB	8
8 KB	8
8 KB	8
8 KB	8
8 KB	8
8 KB	8
8 KB	8
8 KB	8
8 KB	8

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-17 : 07:57:28

[code]DECLARE @Sample TABLE (myData VARCHAR(20))

INSERT @Sample
SELECT '3 KB' UNION ALL
SELECT '16 KB' UNION ALL
SELECT '2 KB' UNION ALL
SELECT '4 KB' UNION ALL
SELECT '43 KB'

SELECT myData
FROM @Sample
ORDER BY LEN(myData) DESC,
myData DESC[/code]

E 12°55'05.25"
N 56°04'39.16"

lols
Posting Yak Master

174 Posts

Posted - 2008-04-17 : 08:00:46

thanks peso for the help. i also did it using CAST.

:)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-04-18 : 05:20:33


SELECT		myData
FROM		@Sample
ORDER BY	substring(mydata,1,charindex(' ',mydata)-1)*1 desc

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources