| Author |
 Topic  |
|
|
Ahoo
Starting Member
6 Posts |
 Posted - 10/03/2012 : 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
USA
1959 Posts |
Posted - 10/03/2012 : 19:06:00
|
Select Distinct RoomType From Rooms order by RoomType asc
-Chad |
 |
|
|
chadmat
The Chadinator
USA
1959 Posts |
Posted - 10/03/2012 : 19:07:37
|
| Oops, missed loft... |
|
|
|
Ahoo
Starting Member
6 Posts |
Posted - 10/03/2012 : 19:16:38
|
| yes the main problem is for loft |
|
|
|
chadmat
The Chadinator
USA
1959 Posts |
Posted - 10/03/2012 : 19:17:12
|
Select Distinct RoomType From Rooms
ORDER BY ISNUMERIC(SUBSTRING(RoomType, 1, 1)) ASC, RoomType
-Chad |
|
|
|
Ahoo
Starting Member
6 Posts |
Posted - 10/03/2012 : 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
USA
1959 Posts |
Posted - 10/03/2012 : 19:21:05
|
Do you need distinct? If so, do it as a subquery
-Chad |
|
|
|
Ahoo
Starting Member
6 Posts |
Posted - 10/03/2012 : 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
USA
1959 Posts |
Posted - 10/03/2012 : 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 - 10/03/2012 : 19:28:42
|
| Thank you Sooooo much. it worked |
|
|
|
waterduck
Aged Yak Warrior
Malaysia
791 Posts |
Posted - 10/03/2012 : 22:38:13
|
SELECT DISTINCT RoomType
FROM Rooms
ORDER BY Len(RoomType), RoomType
|
|
|
| |
Topic |
|
Order by to sorl alphabets first and then numbers
