Order by to sorl alphabets first and then numbers

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 to sorl alphabets first and then numbers

Author

Topic

Ahoo
Starting Member

6 Posts

Posted - 2012-10-03 : 18:58:35

Hello,

my column data is like:

ID Column1
1 1 bedroom
2 2 bedroom
3 3 bedroom
4 4 bedroom
5 loft

How can I write a query to show the output like?

loft
1 bedroom
2 bedroom
3 bedroom
4 bedroom

my query is Select Distinct RoomType From Rooms order by RoomType desc
but it sorts like

loft
4 bedroom
3 bedroom
2 bedroom
1 bedroom
and I don't want it . Thank you so much for help

chadmat
The Chadinator

1974 Posts

Posted - 2012-10-03 : 19:06:00

Select Distinct RoomType From Rooms order by RoomType asc

-Chad

chadmat
The Chadinator

1974 Posts

Posted - 2012-10-03 : 19:07:37

Oops, missed loft...

Ahoo
Starting Member

6 Posts

Posted - 2012-10-03 : 19:16:38

yes the main problem is for loft

chadmat
The Chadinator

1974 Posts

Posted - 2012-10-03 : 19:17:12

Select Distinct RoomType From Rooms
ORDER BY ISNUMERIC(SUBSTRING(RoomType, 1, 1)) ASC, RoomType

-Chad

Ahoo
Starting Member

6 Posts

Posted - 2012-10-03 : 19:19:03

I get this error message :

ORDER BY items must appear in the select list if SELECT DISTINCT is specified

chadmat
The Chadinator

1974 Posts

Posted - 2012-10-03 : 19:21:05

Do you need distinct? If so, do it as a subquery

-Chad

Ahoo
Starting Member

6 Posts

Posted - 2012-10-03 : 19:24:05

yes i need distinct the reason is that in my table the true information is like( based on my design and other columns) :

loft
loft
1 bedroom
1 bedroom
2 bedroom
2 bedroom
3 bedroom
3 bedroom
4 bedroom
4 bedroom

and i want my output like

loft
1 bedroom
2 bedroom
3 bedroom
4 bedroom

chadmat
The Chadinator

1974 Posts

Posted - 2012-10-03 : 19:26:12

So like this:

SELECT RoomType FROM
( SELECT DISTINCT RoomType FROM Rooms) a
ORDER BY ISNUMERIC(SUBSTRING(RoomType, 1, 1)) ASC, RoomType

-Chad

Ahoo
Starting Member

6 Posts

Posted - 2012-10-03 : 19:28:42

Thank you Sooooo much. it worked

waterduck
Aged Yak Warrior

982 Posts

Posted - 2012-10-03 : 22:38:13

SELECT DISTINCT RoomType
FROM Rooms
ORDER BY Len(RoomType), RoomType