select order by house_no

JAYRAM

i have table house no like this below coloum.........

house_no

3-13-10
3-13-200/a
3-13-450/a
3-13-1345/b
3-13-4
3-13-567/c
3-13-832/a
3-13-943

i want table like this....
3-13-4
3-13-10
3-13-200/a
3-13-450/a
3-13-567/c
3-13-832/a
3-13-943
3-13-1345/b

pl write the quarie

6 Replies

Know the answer? Post it — somebody with the same question will find it here.

JAYRAM13y ago
sql
0
Arunkumar T V13y ago
Hi,

Kindly let me know which database you are using.
0
JAYRAM13y ago
SELECT Q.house_no
FROM (
SELECT CONVERT( SUBSTRING_INDEX( SUBSTRING_INDEX( house_no, '-', - 1 ) , '/', 1 ) , UNSIGNED ) AS part1,house_no
FROM Table_name
ORDER BY part1
) AS Q

again it shows error like .....

Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '('.
0
Arunkumar T V13y ago
Hi,

You have added one extra closing bracket near 1. Kindly remove it and check once.
0
JAYRAM13y ago
Thanks for Replay..............

SELECT Q.house_no
FROM (
SELECT CONVERT( SUBSTRING_INDEX( SUBSTRING_INDEX( house_no, '-', - 1 ) , '/', 1 )) , UNSIGNED ) AS part1,house_no
FROM Table_Name
ORDER BY part1
) AS Q

but it shows error like .......

Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '('.

0
Arunkumar T V13y ago
Hi,

Please try the following query and let me know. Hope it works.

SELECT Q.no
FROM (

SELECT CONVERT( SUBSTRING_INDEX( SUBSTRING_INDEX( no, '-', - 1 ) , '/', 1 ) , UNSIGNED ) AS part1, no
FROM house_no
ORDER BY part1

) AS Q

Regards
Arunkumar.T.V
0