CODEWITHSUNDEEP
sqlmysql
Mission
Mission

Reputation: 1297

Order by IF EXISTS

Is there a way to order by column if it actually exists?

ie: SELECT * ORDER BY IF(EXISTS(order_column), order_column ASC, name_column DESC)

Thank you!

Upvotes: 6

Views: 13896

Answers (4)

WASSA
WASSA

Reputation: 33

The best way to do this is create an expression that evaluate the if exists as part of the select query. You can have the expression return order_column or name_column

implemention depends on the SQL you are using, but here you can normallly us IIf(...) but you may need to check for null

Upvotes: 0

Carl Smotricz
Carl Smotricz

Reputation: 67790

Here's my untested guess:

ORDER BY IF(ISNULL(order_column), "", order_column) ASC, name_column DESC

If order_column is NULL, an empty string will be substituted, which won't affect the sort. If it's not NULL, it will be sorted before the name column.

If Mysql won't let you use an expression in the ORDER BY, you can always create an "artificial" column in the SELECT:

SELECT 
  IF (ISNULL(order_column), "     ", order_column) 
     AS my_order_column, 
  name_column 
FROM table 
ORDER BY my_order_column ASC, name_column DESC.

Upvotes: 1

Erlock
Erlock

Reputation: 1968

No, EXISTS must only be used with a subquery and in the WHERE clause. May you be more specific? What do you mean by 'existing column'?

Upvotes: 0

Fermin
Fermin

Reputation: 36111

You could use ISNULL Instead

ORDER BY
    ISNULL(order_column, name_column)

Not sure how you'd add the DESC or ASC tho...

Upvotes: 2

Related Questions