Conditional order by

Question

Conditional order by

yogesh.l.patil 36576

SSC Veteran

Points: 215
More actions
April 25, 2018 at 7:44 pm

#344855

Hi,
I have a situation where I am selecting one column but requires a conditional order by on selected one column.
Data:
Table1
Name
-------
A
B
C
D
E
F
I am checking If name present in table 2 or not.
Table 2:
Name
-----
C
F
Result should be:
C
F
A
B
D
Because C and F present in Table 2 they should come at top.
Then rest of the data.
Sample code:
=================================
IF exists (
Select 'X' from Table1
inner join table 2 on
Table1.Name = Table2.name)
Begin
   SET @strNameExists = 'T'
End
Select Name
from Table1
Order by
CASE
            WHEN @strNameExists = 'T'
            THEN 0
            ELSE 1
        END,
Name
=================================
Example and code is very raw. Sorry for it 🙂
Thank you in advance.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

george_at_sql SSCommitted Points: 1967 More actions · Answer 1

So i tried to make use of straight SQL to solve these kind of problems, ie( without the If-else logic) in T-SQL.
What i did is a left join the two tables using table1.name column with table2.name column. In the result set we desire, the values with NULL in table2.name would be ones which need to be at the bottom of the list,

This part is done by the "order by case when b.name is null then 2 else 1 end" . so the values for 'C' and 'F' would appear on the top of the list, followed by the null values in table2.nameThe second order by is on the table1.name field assuming you would like to see an order on the rest of the elements in table1.name (ie A,B,D,E

Next time post with DDL and DML statements, this can make it easy on others to have a easier start while solving problems.

And if you like my answer , hit the like.
create table table1(name varchar(10));

create table table2(name varchar(10));

insert into table1 values ('A'),('B'),('C'),('D'),('E'),('F');

insert into table2 values ('C'),('F');

select a.name,b.name from table1 a left join table2 b on a.name=b.name order by case when b.name is null then 2 else 1 end ,a.name;

yogesh.l.patil 36576 SSC Veteran Points: 215 More actions · Answer 2

Thank you for answer.
It is useful.

Just a small doubt.
----------
insert into table1 values
('A'),('B'),('C'),('D'),('E'),('F'),(' ');
----
I have one empty value in table1.
After using the above query my output is:

------------
Name
C
F

A
B
D
E
-----------

But ' ' (Empty value) should come as 1st value.
something like:

-------------
Name

C
F
A
B
D
E
----------

george_at_sql SSCommitted Points: 1967 More actions · Answer 3

yogesh.l.patil 36576 - Thursday, April 26, 2018 4:15 AM
Thank you for answer.
It is useful.
Just a small doubt.
----------
insert into table1 values
('A'),('B'),('C'),('D'),('E'),('F'),(' ');
----
I have one empty value in table1.
After using the above query my output is:
------------
Name
C
F
A
B
D
E
-----------
But ' ' (Empty value) should come as 1st value.
something like:
-------------
Name
C
F
A
B
D
E
----------

Yep.
You would specify the order of preference in the first portion of the clause as follows

order by case when b.name is not null then 2
when a.name='' then 1
when b.name is null then 3
end,
a.name