Sorting Problem - SQL Server Forums

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)

Sorting Problem

Author

Topic

tomjacob
Starting Member

8 Posts

Posted - 2002-02-23 : 18:49:47

Hi folks,

I am developing an application where the sort is bit complex.
Sort should be based on 3 fields :

Field1 (varchar 50) NULLABLE
Field2 (int) NOT NULLABLE
Field3 (datetime) NULLABLE

I have to sort the entire DataBase (SQL 7.0) in the same query with the following condition:

If Field1 IS NOT NULL THEN
ORDER BY Field1, Field2 DESC, Field3
Else
ORDER BY Field3, Field2 DESC

Any ideas folks ? I tried CASE/WHEN ..But it is not working for some reasons. Any examples or URLs.

Thanks.

jbkayne
Posting Yak Master

100 Posts

Posted - 2002-02-23 : 19:06:06

Try the following:

order by
CASE
WHEN Field1 is not null then
Field1
ELSE
Field3
END
,
Field2 DESC
,
CASE
WHEN Field1 is not null then
Field3
END

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-02-24 : 04:52:54

I don't understand what the original question is asking for.
Say there are the following rows in the result:


Field1             Field2      Field3
------             ------      ------
AAAAAAA            1           20010101
NULL               2           20020101

How should these be ordered?

vladimir_grigoro
Yak Posting Veteran

62 Posts

Posted - 2002-02-24 : 05:16:48

Try:
~~~~~~~~~~~~
DECLARE @CMD varchar(128)

SET @CMD='...SOME STATEMENTS... ORDER BY '

IF FIELD1 IS NOT NULL
SET @CMD=@CMD+'FIELD1,'

SET @CMD=@CMD+' FIELD2 DESC'

IF FIELD3 IS NOT NULL
SET @CMD=@CMD+', FIELD3'

EXEC (@CMD)
~~~~~~~~~~~~
It could be optimize more and more, but try the idea!

Edited by - vladimir_grigoro on 02/24/2002 05:23:18

tomjacob
Starting Member

8 Posts

Posted - 2002-02-24 : 12:34:39

I'll give an example. Say these are the values :

Field 1 Field2 Field3
------- ------ -------
LMN 3 April 30,2002
NULL 4 May 15, 2002
ABC 3 July 12, 2002
NULL 2 August 15, 2002

The result should be :

ABC 3 July 12, 2002
LMN 3 April 30,2002
NULL 4 May 15, 2002
NULL 2 August 15, 2002

The rule is

IF Field1 is not null then

Field1, Field2 DESC, Field3.
(thats why the first two lines of the above result)

ELSE (that means if Field1 is null)

Field3, Field2 DESC
(thats y the last two rows of the previous result)
(please note: even though 4 is larger than 2 (in field2),
Field 3 is playing the leading role than field2)

quote:

I don't understand what the original question is asking for.
Say there are the following rows in the result:

Field1 Field2 Field3
------ ------ ------
AAAAAAA 1 20010101
NULL 2 20020101

How should these be ordered?

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-02-24 : 13:55:01

So whether or not Field1 IS NULL is actually the first ordering criterion?


ORDER BY CASE WHEN Field1 IS NULL THEN 1 ELSE 0 END,
  Field1, CASE WHEN Field1 IS NULL THEN Field3 END, Field2 DESC, Field3

or more simply, if you know the largest possible Field1 value:


ORDER BY COALESCE(Field1, 'ZZZZZZZZZZZZ'),
  CASE WHEN Field1 IS NULL THEN Field3 END, Field2 DESC, Field3

Edited by - Arnold Fribble on 02/24/2002 14:00:15

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources