ORDER BY with decimal points - 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

General SQL Server Forums

New to SQL Server Programming

ORDER BY with decimal points

Author

Topic

Iguana
Starting Member

5 Posts

Posted - 2009-07-27 : 09:31:48

Hi people,

Reasonably new to SQL and having some problems getting my head round this. Any help would be appreciated:

Basically, I am trying to retreive all the records in a table and display them in order of their reference number. Kind of like a book where the reference numbers are:
1.1
1.2
1.3
all the way up to
1.10

However, when I run my query:

SELECT * FROM tbl_test ORDER BY ref asc

the page returns:
1.1
1.10
1.2
1.3

so 1.10 is picked up before 1.2 arrgghhhhhh

Is there a simple way of getting it to order correctly using a query. Also, what is the best datatype to use? I have treid decimal but that stores 1.10 as 1.1 and disposes of the 0

Any ideas. Thanks in advance. Going up the wall!

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-07-27 : 09:40:48

Hi

declare @table table
(
PK VARCHAR(10)

)
insert into @table
select '1.1' union all
select '1.10' union all
select '1.2' union all
select '1.3'

select * from @table ORDER BY LEN(PK),PK

-------------------------
R..

Iguana
Starting Member

5 Posts

Posted - 2009-07-27 : 09:44:52

thanks very much fro replying though not sure what you mean? can you give me small explanation of what's happening here:

quote:
Originally posted by rajdaksha

Hi

declare @table table
(
PK VARCHAR(10)

)
insert into @table
select '1.1' union all
select '1.10' union all
select '1.2' union all
select '1.3'

select * from @table ORDER BY LEN(PK),PK

-------------------------
R..

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-07-27 : 09:50:32

hi

Just run this....
declare @table table
(
PK VARCHAR(10)

)
insert into @table
select '1.1' union all
select '1.10' union all
select '1.2' union all
select '1.3'

SELECT LEN(PK)as SORT,PK FROM @TABLE

SELECT LEN(PK)as SORT,PK FROM @TABLE ORDER BY LEN(PK)

-------------------------
R..

Iguana
Starting Member

5 Posts

Posted - 2009-07-27 : 09:57:52

Thankyou very much for the help!!!!

Much appreciated - lesson learnt

quote:
Originally posted by rajdaksha

hi

Just run this....
declare @table table
(
PK VARCHAR(10)

)
insert into @table
select '1.1' union all
select '1.10' union all
select '1.2' union all
select '1.3'

SELECT LEN(PK)as SORT,PK FROM @TABLE

SELECT LEN(PK)as SORT,PK FROM @TABLE ORDER BY LEN(PK)

-------------------------
R..

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-07-27 : 10:00:40

What happen if the data like this

select '1.1' union all
select '1.10' union all
select '1.20' union all
select '1.3'

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-07-27 : 10:03:49

Try this

select cast(pk as decimal(10,2)) from @table order by cast(pk as decimal(10,2))

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-07-27 : 10:04:27

hi

DECLARE @TABLE TABLE
(
PK VARCHAR(10)

)
INSERT INTO @TABLE
SELECT '1.1' UNION ALL
SELECT '1.10' UNION ALL
SELECT '1.20' UNION ALL
SELECT '1.3'

SELECT LEN(PK)AS SORT,PK FROM @TABLE ORDER BY LEN(PK),PK

/*
SORT PK
----------- ----------
3 1.1
3 1.3
4 1.10
4 1.20

(4 row(s) affected)*/

-------------------------
R..

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-07-27 : 10:09:49

quote:
Originally posted by rajdaksha

hi

DECLARE @TABLE TABLE
(
PK VARCHAR(10)

)
INSERT INTO @TABLE
SELECT '1.1' UNION ALL
SELECT '1.10' UNION ALL
SELECT '1.20' UNION ALL
SELECT '1.3'

SELECT LEN(PK)AS SORT,PK FROM @TABLE ORDER BY LEN(PK),PK

/*
SORT PK
----------- ----------
3 1.1
3 1.3
4 1.10
4 1.20

(4 row(s) affected)*/

-------------------------
R..

Here the Order is not as expected

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-07-27 : 10:13:54

Hi

How can you give some example.....for that i will learn from you...

-------------------------
R..

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-07-27 : 12:03:24

There are a bunch of ways to do this, here is yet another way:

SELECT *
FROM @Table
ORDER BY
	CAST(PARSENAME(PK, 2) AS INT),
	CAST(PARSENAME(PK, 1) AS INT)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-07-28 : 03:07:40

http://sqlblogcasts.com/blogs/madhivanan/archive/2008/07/21/ordering-interger-values-stored-in-varchar-column.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources