-2

I'm reviewing some SQL statement which uses a COALESCE statement in an ORDER BY. I've read the docs about COALESCE so i'm trying to understand if I understand how it's getting used, in this scenario.

This is the part of the sql statement:

ORDER BY COALESCE([UpdatedOn], [CreatedOn]) DESC

Is it trying to say this (via some fake data I've made up):

** Note: DateFormat is yyyy-mm-dd

 ELID |  LK   |  CreatedOn |  UpdatedOn | Computed-OrderBy
+---------------------------------------------------------
    1 | 'aaa' | 2020-01-01 | 2020-01-02 | 2020-01-02
    2 | 'bbb' | 2020-01-01 |       NULL | 2020-01-01

so then, that is the result (in memory, etc) and SQL Server will order the results in the Computer-OrderBy column (which we never really see)

So the results would be ordered:

ELID 2 - 2020-01-01
ELID 1 - 2020-01-02

UPDATE 1: If the COALESCE was in a SELECT statement, then I wouldn't be asking a question because I grok it. I'm asking about when ORDER BY 'ing .. how SQL sorta does it, behind the scenes. Notice how I've made up a new column Computed-OrderBy ? Well, I was hoping to see if that's more-or-less what Sql Server does ... so it knows how to order by those 'computed' values.

Pure.Krome
  • 84,693
  • 113
  • 396
  • 647
  • 1
    So, what are you actually asking here, what does `COALESCE` do? Have you reviewed the [documentation](https://learn.microsoft.com/en-us/sql/t-sql/language-elements/coalesce-transact-sql?view=sql-server-ver15)? – Thom A Aug 17 '20 at 08:34
  • Yes, I'm trying to see if my understanding of COALESCE is correct. As in, behind the scenes this is what is going on. I'll update my post to reflect your question. – Pure.Krome Aug 17 '20 at 08:34
  • Put `COALESCE([UpdatedOn], [CreatedOn])` in the SELECT list to see what it returns. – jarlh Aug 17 '20 at 08:38
  • @jarlh I'm trying to see ... not the records that were returned .. but how SQL SERVER handles the `COALESCE` in the `ORDER BY`. So if you look at my sample data .. that was me pretending to be SQL SERVER .. with some in memory temp result, before it does the ORDERING. – Pure.Krome Aug 17 '20 at 08:41
  • COALESCE will move from left to right, and use the first value it finds that isn't NULL. So your row with ELID = 1 would be "computed" to be 2020-01-01. COALESCE basically says "use one of the following, the first one from left to right that isn't NULL". – Captain Kenpachi Aug 17 '20 at 08:44
  • 1
    Thanks @CaptainKenpachi for the comment. So how I wrote up the 'fake data' .. is just like you're saying, then? – Pure.Krome Aug 17 '20 at 11:20
  • 1
    Oh yes, you have the order reversed in your statement, so it's correct then. – Captain Kenpachi Aug 17 '20 at 11:21
  • thank you! can you please make an answer then so I can give you the points, plz. – Pure.Krome Aug 17 '20 at 11:37

1 Answers1

2

As per the documentation on COALESCE (Transact-SQL) it does the following:

Evaluates the arguments in order and returns the current value of the first expression that initially doesn't evaluate to NULL. For example, SELECT COALESCE(NULL, NULL, 'third_value', 'fourth_value'); returns the third value because the third value is the first value that isn't null.

The fact that COALESCE is in the ORDER BY doesn't change that. COALESCE will return the value of UpdatedOn if it is a non-NULL value, and then then value of CreatedOn. If both are NULL then NULL will be returned.

As the expression is in the ORDER BY, then your results will then be put in (ascending) order based on the result of the expression, with rows that have NULL returned for the expression being treated as having the lowest value, and the date 9999-12-31 having the greatest.

Thom A
  • 88,727
  • 11
  • 45
  • 75