Sql-server – Query Relating Rows W/O A Corresponding Value

I've used MySQL, but it should apply to virtually all database servers.

CREATE TABLE `test1` 
(
  `user` varchar(10) DEFAULT NULL,
  `logintype` int(11) DEFAULT NULL
);



mysql> SELECT * FROM test1;
+------+-----------+
| user | logintype |
+------+-----------+
| Bob  |         1 |
| Ryan |         1 |
| Tom  |         1 |
| Tom  |         3 |  <<== ALSO WORKS IF YOU HAVE ANOTHER 
+------+-----------+       RECORD TOM, 5
4 rows in set (0.00 sec)

mysql> 

SELECT t1.user, t1.logintype  
FROM test1 t1 
INNER JOIN test1 t2  
ON t1.user = t2.user 
AND t1.logintype != t2.logintype 
GROUP BY t1.user, t1.logintype  
ORDER BY logintype;    

+------+-----------+
| user | logintype |
+------+-----------+
| Tom  |         1 |
| Tom  |         3 |
+------+-----------+

Itzik Ben-Gan's blog post (link provided by Daniel Hutmacher) has some nice solutions to the problem for SQL Server 2012 and later:

The Last non NULL Puzzle.

Here is one more that will work in older versions (even 2005) and can be easily adapted for more columns. I haven't tested for efficiency or compared to the other solutions but I'd expect it to work better if the table had a clustered index on (id, priority). But performance may depend on many other things, like the distribution, the number of distinct id values, the number of versions (rows per id values), the number and types of columns, etc.:

SELECT 
    d.id,
    d1.col1,
    d2.col2,
    d3.col3
FROM 
    ( SELECT id, @point_in_time AS priority 
      FROM tablename
      GROUP BY id
    ) AS d
  OUTER APPLY
    ( SELECT TOP (1) col1
      FROM tablename 
      WHERE id = d.id
        AND priority <= d.priority          -- optional
        AND col1 IS NOT NULL
      ORDER BY priority DESC
    ) AS d1 
  OUTER APPLY
    ( SELECT TOP (1) col2
      FROM tablename 
      WHERE id = d.id
        AND priority <= d.priority          -- optional
        AND col2 IS NOT NULL
      ORDER BY priority DESC
    ) AS d2 
  OUTER APPLY
    ( SELECT TOP (1) col3
      FROM tablename 
      WHERE id = d.id
        AND priority <= d.priority          -- optional
        AND col3 IS NOT NULL
      ORDER BY priority DESC
    ) AS d3 ;

Test in SQLfiddle.

The @point_in_time above is a parameter, that can be removed if we want the latest non-null values across all the table.

If however, we want the latest non-null values for a specific point in time (say for when priority=55), we can replace that @point_in_time with 55 and have the latest non-null values up to that point in time (55).