ORDER BY CLAUSE IN SQL SERVER SQL QUERY

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

SQL Server 2000 Forums

SQL Server Development (2000)

ORDER BY CLAUSE IN SQL SERVER SQL QUERY

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-03-19 : 11:25:12

MANMOHAN writes "DEAR SIR,
I AM HAVING THE STRUCTURE OF THE TABLE AS FOLLOWS

EMP_CODE NVARCHAR 10
HOUSE_NO NVARCHAR 10

HERE THE PROBLEM ARISES OF SORTING THE DATA IN SQL STATEMENT.

THE ENTRIES OF THE TABLE MAY BE

EMP_CODE    HOUSE_NO

     1           12/1          
     22          12/2
     202         22   
     212         13

WHILE SPECIFYING BOTH THE FIELDS IN "ORDER BY " CLAUSE I AM NOT GETTING THE DESIRED RESULT SORTED IN
"EMP_CODE,HOUSE_NO" ORDER
AS HOUSE NOs MUST BE 12/1,12/2,13,22 ETC.
PLEASE REPLY SOON .. HOW TO SORT THE NVARCHAR FIELDS"

Jay99

468 Posts

Posted - 2002-03-19 : 11:48:01

IF YOU WANT TO SORT BY HOUSE_NO FIRST, THEN PUT HOUSE_NO IN YOUR ORDER BY CLAUSE FIRST.

Jay
<O>

MANMOHAN
Starting Member

1 Post

Posted - 2002-03-20 : 01:01:06

DEAR SIR,
MY PROBLEM IS NOT SOLVED WITH THIS AS THE FIELD CONTAINS "/" AND ON SORTING THIS MAKES TROUBLE.HOW TO CONSIDER THIS WHILE ORDERING THE TABLE.

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2002-03-20 : 01:08:45

Please don't post in all Caps Lock. It's really annoying.

Firstly, EMP_CODE is an integer, why store it in nvarchar ?
Secondly, I would split HOUSE_NO into 2 separate fields, make them Integers and you won't have the problem any more.

Damian

joldham
Wiseass Yak Posting Master

300 Posts

Posted - 2002-03-20 : 08:21:24

To expand on what Merkin said:

If you absolutely must have the / in your returned queries then you could cast the house_no and the new created column as varchars and then concatenate the strings.

EMP_CODE HOUSE_NO NEW_COLUMN
1 12 1
22 12 2
202 22
212 13

SELECT EMP_CODE, CASE
WHEN NEW_COLUMN > 0 then CAST(house_no as varchar(10)) + '/' + CAST(new_column as varchar(10))
ELSE cast(house_no as varchar(10))
END HOUSE_NO
FROM houses
ORDER BY HOUSE_NO, NEW_COLUMN

Jeremy

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources