SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Order by to sorl alphabets first and then numbers
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Ahoo
Starting Member

6 Posts

Posted - 10/03/2012 :  18:58:35  Show Profile  Reply with Quote
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

USA
1974 Posts

Posted - 10/03/2012 :  19:06:00  Show Profile  Visit chadmat's Homepage  Reply with Quote
Select Distinct RoomType From Rooms order by RoomType asc

-Chad
Go to Top of Page

chadmat
The Chadinator

USA
1974 Posts

Posted - 10/03/2012 :  19:07:37  Show Profile  Visit chadmat's Homepage  Reply with Quote
Oops, missed loft...
Go to Top of Page

Ahoo
Starting Member

6 Posts

Posted - 10/03/2012 :  19:16:38  Show Profile  Reply with Quote
yes the main problem is for loft
Go to Top of Page

chadmat
The Chadinator

USA
1974 Posts

Posted - 10/03/2012 :  19:17:12  Show Profile  Visit chadmat's Homepage  Reply with Quote
Select Distinct RoomType From Rooms
ORDER BY ISNUMERIC(SUBSTRING(RoomType, 1, 1)) ASC, RoomType

-Chad
Go to Top of Page

Ahoo
Starting Member

6 Posts

Posted - 10/03/2012 :  19:19:03  Show Profile  Reply with Quote
I get this error message :

ORDER BY items must appear in the select list if SELECT DISTINCT is specified
Go to Top of Page

chadmat
The Chadinator

USA
1974 Posts

Posted - 10/03/2012 :  19:21:05  Show Profile  Visit chadmat's Homepage  Reply with Quote
Do you need distinct? If so, do it as a subquery

-Chad
Go to Top of Page

Ahoo
Starting Member

6 Posts

Posted - 10/03/2012 :  19:24:05  Show Profile  Reply with Quote
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
Go to Top of Page

chadmat
The Chadinator

USA
1974 Posts

Posted - 10/03/2012 :  19:26:12  Show Profile  Visit chadmat's Homepage  Reply with Quote
So like this:

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


-Chad
Go to Top of Page

Ahoo
Starting Member

6 Posts

Posted - 10/03/2012 :  19:28:42  Show Profile  Reply with Quote
Thank you Sooooo much. it worked
Go to Top of Page

waterduck
Aged Yak Warrior

Malaysia
961 Posts

Posted - 10/03/2012 :  22:38:13  Show Profile  Reply with Quote
SELECT DISTINCT RoomType
FROM Rooms
ORDER BY Len(RoomType), RoomType
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000