Advance Order By - 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 2000 Forums

Transact-SQL (2000)

Advance Order By

Author

Topic

Danny4003
Starting Member

40 Posts

Posted - 2006-10-24 : 15:14:16

Hi My Name is Danny,

I have a problem with the Order By Clause.

My scenario is that I have Varible Data inside a Table ie: A123, B123, 1, 2 , 10, 11,13, 33
(this is just Pseudo)
Well when I do an Order By Clause it checks the numbers and The Alphabets. The Alphabets with the numbers Work accordingly but the numbers are different. It checks the Numbers ie: 1, 10, 11, 13, 2, 33. Do you think that you can help me out with this Situation. I think I have to do some sort of Advance Order By Clause. The only problem is that I do not know how. Another solution is the Case Function. I just started working with SQL and I'm very Intermediate with this Program. If you can help Thx in advance, If not thx for reading this email and giving me some of your needed time.

Thank you ,
Danny Dubroc

Danny4003
Starting Member

40 Posts

Posted - 2006-10-24 : 15:43:22

The Code that I was using was like this:
Select Vol,Page From ConveyanceTbl
Order By Vol
------Results------
Vol Page
1 33
12 45
13 55
2 105
25 205
33 305
A1 100
B2 102
C2 104

PSamsig
Constraint Violating Yak Guru

384 Posts

Posted - 2006-10-24 : 15:48:51

[code]DECLARE @Table TABLE (Data varchar(10))

INSERT INTO @Table (Data)
SELECT 'A123' UNION ALL
SELECT 'B123' UNION ALL
SELECT '1' UNION ALL
SELECT '2' UNION ALL
SELECT '10' UNION ALL
SELECT '11' UNION ALL
SELECT '13' UNION ALL
SELECT '33'

SELECT *
FROM @Table
ORDER BY CASE WHEN ISNUMERIC(Data) <> 0 THEN RIGHT(REPLICATE(' ', 5) + Data, 5) ELSE Data END[/code]
result:
1
2
10
11
13
33
A123
B123

be awarew though, ISNUMERIC() has some strange oddeties (look at: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=13234)

-- Alice came to a fork in the road. "Which road do I take?" she asked. "Where do you want to go?" responded the Cheshire cat. "I don't know," Alice answered. "Then," said the cat, "it doesn't matter."

Danny4003
Starting Member

40 Posts

Posted - 2006-10-24 : 16:34:56

hey man thx so much for your help. I would like to request your help when needed or if i can send you questions through email or yahoo im. instead of the forum. Or would you rather me use the forum.
If you have you any questions for me even though im still a rookie. lol I would be very abliged to help

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-10-24 : 16:54:14

Always use the forum to ask questions.

Tara Kizer

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-10-24 : 23:22:11

Also try

SELECT *
FROM @Table
ORDER BY LEN(data),data

Note: I didnt test this

Madhivanan

Failing to plan is Planning to fail

PSamsig
Constraint Violating Yak Guru

384 Posts

Posted - 2006-10-25 : 02:47:14

I like that one Madhdi, simple and no hidden pitfalls, it wont handle leading zeros, but it wans part of the assignment.

-- Alice came to a fork in the road. "Which road do I take?" she asked. "Where do you want to go?" responded the Cheshire cat. "I don't know," Alice answered. "Then," said the cat, "it doesn't matter."

Danny4003
Starting Member

40 Posts

Posted - 2006-10-25 : 16:26:07

thx both of you for your support. I really appreciate your help and I will continued to post here use helpful information from here. Oh the code sent by Madhdi work just as well as Samsig thx.

I can not stress enough how thankful I am to both of you and this forum.

Danny D.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources