Order by Column1 If Column1 Is Not Null, Otherwise Order by Column2

Order by Column1 if Column1 is not null, otherwise order by Column2

Something like:

ORDER BY CASE 
    WHEN Column1 IS NOT NULL THEN Column1
    ELSE Column2
END

Or the corresponding syntactic sugar:

ORDER BY COALESCE(Column1, Column2)

Note that the datatype of the two columns must be comparable (the RDBMS defines the rules).

Concat column1 and column2 if column1 does not contain column2

For Firebird, you can use a CASE with CONTAINING or POSITION in combination with the concatenation operator (||):

case when column2 is null or column1 containing column2 then column1 else column1 || column2 end

or

column1 || case when column1 not containing column2 then column2 else '' end

See this Firebird dbfiddle. Using position would be similar.

PostgreSQL doesn't have CONTAINING, so there you'd use POSITION(column2 IN column1) > 0 instead of CONTAINING or POSITION(column2 IN column1) = 0 for NOT CONTAINING:

case when column2 is null or position(column2 in column1) > 0 then column1 else column1 || column2 end

or

column1 || case when position(column2 in column1) = 0 then column2 else '' end

See this PostgreSQL dbfiddle. This use of POSITION would also work in Firebird.

SQL: how do i order by a field if its not null else use another field

It seems like I'm giving this advice three times a week here on SO, maybe I should just give up and let it go :-) Nah, I don't think so:

Don't use per-row functions in your column calculations (or order by clauses) if you want your database to scale well. You should check performance for your particular case (measure, don't guess) but doing calculations when reading the database will generally affect your ability to scale (this won't matter for your address book database but the shops I work in have huge amounts of data).

The number of "how do I get my DB to go faster?" questions far outweighs the number of "how do I use less space?" ones. It's a well-trodden path to sacrifice disk space for performance.

The right time to do calculations is when the data changes. That way the cost of the changes is amortised across all reads.

My advice is to create another column such as dtLastAction to contain the ordering value then use an insert/update trigger to set it to the same as dtModified if not null, or dtPosted if dtModified is null. Technically, this violates 3NF but that's okay if you know what you're doing, and the triggers guarantee data consistency in that case.

Then index on the dtLastAction column and see the speed of your queries improve at the (lesser) cost of some extra work during inserts and updates. I say lesser because the vast majority of database tables are read more often than written (obviously this method is useless if your particular situation is one of the very rare exceptions).

Alternatively, for this particular case, you could set dtModified and dtPosted to the same value when creating the entry, meaning that dtModified will never be null. You can still detect posts that have never been modified by the fact that these two datetime values are identical.

CASE WHEN column1 IS NULL THEN NULL ELSE column2 END

Try following,

SELECT
    id
    , content -- THIS IS JUST TO COMPARE, WE DON'T NEED THIS
    , (CASE WHEN id IS NULL THEN NULL ELSE content END) as editedContent -- WE NEED THIS(WITH NULL VALUE IN THE END)
    , SUM(upvote)
FROM `test`.`comment`
GROUP BY id 

Union all

select 
    'null',
    'null',
    'null',
    SUM(upvote)
from comment

Select Column2 if Column1 is Empty or NULL

SELECT CASE WHEN Column1 <> '' THEN Column1 ELSE Column2 END  

(Column1 <> '' doesn't evaluate to true when it is NULL either)

Or

SELECT COALESCE(NULLIF(Column1,''),Column2)

SQL - Null value should match with not null value with another table

You could do this as a union:

select Column1 from
(
select Table1.Column1 as Column1,Table1.Column2 as Column2
from Table1
join Table2 on
  Table1.Column2=Table2.Column2 and Table1.Column3=Table2.Column3
UNION
select Table1.Column1 as Column1,Table1.Column2 as Column2
from Table1
join Table2 on
  Table1.Column2 is NULL and Table1.Column3=Table2.Column3
) as Unioned
ORDER BY Column2 NULLS LAST
LIMIT 1;

The UNION discards duplicates, but you could always wrap the entire statement in another SELECT if you explicitly need to tell it to return at most one value.

SQL : Updating a value for Column1 and moving the old value of the Column1 to Column2 at the same time

you can use case expression to evaluate the 3 situations. #1, #2, #3 are your required situations

update d
set    slitting_win  = case when d.slitting_win is null or d.slitting_win = 0 -- #1
                            then @slt
                            when d.slitting_win = @slt -- #2
                            then d.slitting_win                             
                            else @slt  -- #3
                            end,
       slitting_win2 = case when slitting_win is null or slitting_win = 0 -- #1
                            then d.slitting_win2
                            when d.slitting_win = @slt -- #2
                            then d.slitting_win2
                            else d.slitting_win -- #3
                            end
from   daily_wastage_follow_up d
where  d.[date] = @date

---

Related Topics