sort record by sno - 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

sort record by sno

Author

Topic

shanmugaraj
Posting Yak Master

219 Posts

Posted - 2006-03-28 : 07:25:57

i have to sort the records in the table. When i write

select sno from employers order by sno

Result ::
1
10
11
12
2
20
21
22

i want the result as

1
2
10
11
12
21
22

can you Kindly tell me how to get the records listed

ravilobo
Master Smack Fu Yak Hacker

1184 Posts

Posted - 2006-03-28 : 07:40:49

Your sno field is not of integer data type. either change it to integer data type or use CONVERT function...

------------------------
I think, therefore I am - Rene Descartes

shanmugaraj
Posting Yak Master

219 Posts

Posted - 2006-03-28 : 07:51:24

Kindly help me .. how to use, i want to list the field in the dropdown list , the field is non mumeric. how to use convert ?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-03-28 : 07:52:07

If you dont want to allow alpahbets on the sno make it as integer datatype

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-03-28 : 07:53:11

Order by convert(int,sno)

Madhivanan

Failing to plan is Planning to fail

shanmugaraj
Posting Yak Master

219 Posts

Posted - 2006-03-28 : 08:03:27

I have got the result
Thank you mr.Madhivanan

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-03-28 : 08:10:45

But it is better to use int datatype if you want them to be numbers

Madhivanan

Failing to plan is Planning to fail

ravilobo
Master Smack Fu Yak Hacker

1184 Posts

Posted - 2006-03-28 : 08:13:02

quote:
Originally posted by madhivanan

Order by convert(int,sno)

--How about the following....

where isnumeric(sno)=1 Order by convert(int,sno)

------------------------
I think, therefore I am - Rene Descartes

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-03-28 : 08:14:23

IsNumeric is not reliable
Read this fully
http://aspfaq.com/show.asp?id=2390

Madhivanan

Failing to plan is Planning to fail

shanmugaraj
Posting Yak Master

219 Posts

Posted - 2006-03-28 : 09:12:41

when i try
SELECT SSN FROM MEMBERS ORDER BY CONVERT(int, SSN)
Error ::

syntax error converting the varchar value 'asdf' to a column of data type int.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-03-28 : 09:15:31

Try

Order by len(SSN),SSN

Madhivanan

Failing to plan is Planning to fail

shanmugaraj
Posting Yak Master

219 Posts

Posted - 2006-03-28 : 09:23:49

it is working...
where i should use
ORDER BY LEN(SSN), SSN
ORDER BY CONVERT(int, SSN)

thanks a lot,,

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-03-28 : 09:26:59

ORDER BY LEN(SSN), SSN for alphanumeric data
ORDER BY CONVERT(int, SSN) for numeric data

But ORDER BY LEN(SSN), SSN will work for all cases

Madhivanan

Failing to plan is Planning to fail

shanmugaraj
Posting Yak Master

219 Posts

Posted - 2006-03-28 : 09:36:07

ok .. thanks again ..
now in any criteria , i will always use

ORDER BY LEN(SSN), SSN

nr
SQLTeam MVY

12543 Posts

Posted - 2006-03-28 : 09:49:45

It's a bad idea to use it when it's not necessary as it could cause it to not use an index when it could.

For character fields that contain numericish data I use

order by right(space(20) + fld, 20)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

shanmugaraj
Posting Yak Master

219 Posts

Posted - 2006-03-29 : 01:18:54

when i use

select Employer_Number from employers order by len(Employer_Number) , Employer_Number
i get the repeated Employer_Number
so i tried

select distinct Employer_Number from employers order by len(Employer_Number) , Employer_Number
select Employer_Number from employers order by len(Employer_Number) , Employer_Number
group by Employer_Number

but i cannot use. can you tell me how to select distinct of employernumber

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-03-29 : 01:40:59

Did you get error when you run those queries?

Madhivanan

Failing to plan is Planning to fail

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-03-29 : 01:44:17

this thread continues over here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=63876

KH
Choice is an illusion, created between those with power, and those without.
Concordantly, while your first question may be the most pertinent, you may or may not realize it is also the most irrelevant

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources