Getting multiple MAX values from one-to-many table

Question

I have two tables and I would like to get MAX(date) from one-to-many table. If there is no value, it should be NULL. Only way I know how to do it by making sub queries but if there is ~20 different types then 20 sub queries does not sound efficient enough. Is there any better way to do it?

Table A:
UserId    | Name
1         | John
2         | Jane

Table B:
UserId   | Type    | Date
1        | A       | 2015-01-01
1        | A       | 2015-12-31
1        | B       | 2015-01-01
1        | B       | 2015-12-31
2        | B       | 2015-06-06
1        | C       | 2015-01-01
2        | C       | 2015-09-09

Result:
UserId | Type A date | Type B date | Type C date
1      | 2015-12-31  | 2015-12-31  | NULL
2      | NULL        | 2015-06-06  | 2015-09-09

Current solution:

 SELECT UserId,
        (SELECT MAX(Date) FROM B WHERE Type = 'A' AND B.UserId= A.UserId),
        (SELECT MAX(Date) FROM B WHERE Type = 'B' AND B.UserId= A.UserId),
        (SELECT MAX(Date) FROM B WHERE Type = 'C' AND B.UserId= A.UserId
        AND Date > (SELECT MAX(Date) FROM B WHERE Type = 'B' AND B.UserId =   A.UserId))
    FROM A

Thank you for all quick answers! They work perfectly. I modified my question little bit since I noticed that I need to add some conditions on some types. For example. Type C should be only presented if it's bigger than type B.

Answer 1

To get the expected result you don't even need to join, simply do a group by and use case expressions do to conditional aggregation:

select userid,
       max(case when type = 'A' then date end) Adate,
       max(case when type = 'B' then date end) Bdate
from tableB
group by userid

If you also want the username, you can join tableA with the above query:

select a.name, b.Adate, b.Bdate
from tableA a
join (select userid,
             max(case when type = 'A' then date end) Adate,
             max(case when type = 'B' then date end) Bdate
      from tableB
      group by userid) b
    on a.userid = b.userid

However, if the number of types is unknown, I'd group by the column type too. I.e return types in different rows.

select a.name, b.type, b.date
from tableA a
left join (select userid, type, max(date) date,
           from tableB
           group by userid, type) b
    on a.userid = b.userid

Answer 2

You can do a conditional aggregation instead of the subqueries in the SELECT:

SELECT
    a.UserId,
    MAX(CASE WHEN b.Type = 'A' THEN b.date END) AS ADate,
    MAX(CASE WHEN b.Type = 'B' THEN b.date END) AS BDate
FROM TableA a
LEFT JOIN TableB b
    ON b.UserId = a.UserId
GROUP BY a.UserId;

However, the above will only work if you know the values of Type. If you don't, then you need to do it using a dynamic query.

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql = 
'SELECT
    a.UserId' + CHAR(10) +
(SELECT DISTINCT 
'   , MAX(CASE WHEN b.Type = ''' + Type + ''' THEN b.Date END) AS ' + QUOTENAME(Type + 'Date') + CHAR(10)
FROM TableB
FOR XML PATH('')
) +
'FROM TableA a
LEFT JOIN TableB b
    ON b.UserId = a.UserId
GROUP BY a.UserId;';

EXEC (@sql);

ONLINE DEMO

Reference: Cross Tabs and Pivots by Jeff Moden

Answer 3

Use Dynamic Pivot, no matter how many type you have, you dont need to change the query.

DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)

SELECT @ColumnName = ISNULL(@ColumnName + ',','') + QUOTENAME([TYPE])
FROM (SELECT DISTINCT [TYPE] as [TYPE] FROM [master].[dbo].[YourTable]) AS TypeTable

SET @DynamicPivotQuery = 
'SELECT USERID,' + @ColumnName+'
FROM [master].[dbo].[YourTable]
PIVOT(MAX([Date]) 
      FOR [TYPE] IN (' + @ColumnName+')) AS PVTTable'

EXEC sp_executesql @DynamicPivotQuery

Result is like below :

USERID  A           B
1       2015-12-31  2015-12-31
2       NULL        2015-06-06

Edit : For type C,there are some condition:

DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)
DECLARE @ColumnNameForDisplay AS NVARCHAR(MAX)
DECLARE @otherCondition AS NVARCHAR(MAX)

SELECT @ColumnName = ISNULL(@ColumnName + ',','') + QUOTENAME([TYPE])
FROM (SELECT DISTINCT [TYPE] as [TYPE] FROM [master].[dbo].[YourTable]) AS TypeTable

SELECT @ColumnNameForDisplay = ISNULL(@ColumnNameForDisplay + ',','') + [TYPE2]
FROM (SELECT DISTINCT [TYPE]+' as [Type '+[TYPE]+' date]' as [TYPE2] FROM [master].[dbo].[YourTable] where type <> 'c') AS TypeTable

SELECT @otherCondition = ' ,Case when c <= b then null else c END as [Type C date] '


SET @DynamicPivotQuery = '
SELECT USERID,'+@ColumnNameForDisplay+@otherCondition+' from(
SELECT USERID,' + @ColumnName+'
FROM [master].[dbo].[YourTable]
PIVOT(MAX([Date]) 
FOR [TYPE] IN (' + @ColumnName+')) AS PVTTable) as t'

EXEC sp_executesql @DynamicPivotQuery

Result is like below :

USERID  Type A date Type B date Type C date
1       2015-12-31  2015-12-31  NULL
2       NULL        2015-06-06  2015-09-09