Geek Answers Handbook

Conditional ORDER BY depending on column values

I need to write a query that does this:

SELECT TOP 1 FROM a list of tables (Joins, etc) ORDER BY Column X, Column Y, Column Z

If ColumnX is NOT NULL , then at the moment I am reselecting using a slightly different ORDER BY .

So, I am doing the same request twice. If the first has NULL in a specific column, I am returning this row from my procedure. However, if the value is not NULL - I need to make another identical select, except, the order of another column or two.

Now I select it in the temp table for the first time. Then check the value of the column. If everything is in order, return the temp table, otherwise, repeat the selection and return this result set.

More details:

In English, the question I ask in the database is:

Bring back my results for a specific look (by indexed foreign key). I expect about 1000 lines. Order it by appearance date (column, not indexed, nullified), last appearance first. Check "importId". If the import identifier is not NULL for this top 1 row, then we need to run the same query, but this time order by import identifier (last first) and return this row. Or just return the first row from the original query.

+4
source share
3 answers

I would say that the best way to do this in one query is with the CASE statement ...

 SELECT TOP 1 FROM ... ORDER BY (CASE WHEN column1 IS NULL THEN column2 ELSE column1 END)
+3
source

You can use COALESCE to turn null columns into ordered friendly values.

  SELECT CAST(COALESCE(MyColumn, 0) AS money) AS Column1 FROM MyTable ORDER BY Column1;
+1
source

I used in Firebird (numeric columns):

 ORDER BY CASE <condition> WHEN <value> THEN <column1>*1000 + <column2> ELSE <column3>*1000 + <column4> END
0
source

More articles:


All Articles