I have a table with the following format:

id      description1       description2
123     null               abcd
123     zyxw               abcd
124     null               efgh
124     vuts               efgh
125     null               ijkl
126     null               mnop

Basically, I want SQL to return a single record for 123 and 124 that contains description1, instead of displaying two records for each, one with a null description1. But if there is no description1 in any record of the same id and description2, simply display null. So that the data displayed would be:

id    description1    description2
123   zyxw            abcd
124   vuts            efgh
125   null            ijkl
126   null            mnop
description1 = MAX(description1),
description2 = MAX(description2)
FROM dbo.MyTable
  • 1
    This is what i'd use as well, but keep in mind that using MAX will not just evaluate 'NULL' vs abcd, but also 'abcd' vs 'defg' (of which 'abcd' will win if you're ordering by id ASC). As long as OP doesn't need one NON-NULL value over another NON-NULL value(s), he should be okay with this one.
    – SQLDevDBA
    Aug 24 '16 at 18:17

With Teradata, I was able to solve this with:

SELECT id, description1, description2 FROM dbo.MyTable
QUALIFY ROW_NUMBER() OVER (PARTITION BY id ORDER BY description1 nulls last) = 1

However, since I didn't specify Teradata and Andy's generic answer works great, it's definitely the best!

  • 1
    Andy's answer isn't "generic" it's non-standard SQL because you can't assign variables like that in SQL. Aug 24 '16 at 21:20
  • I assumed he meant MAX(description1) as description1 Aug 25 '16 at 21:15

Not the answer you're looking for? Browse other questions tagged or ask your own question.