CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Rian Schmits
Rian Schmits

Reputation: 3206

SQL: Aggregate after join with null check

I have these tables (in SQL-Server 2008 R2):

Table1:

Id   Guid
1    {530D8FE1-7541-43CC-9F92-1AA776490155}
2    {CAC5B001-C8DE-46AA-A848-5D831633D0DF}
3    NULL

Table2:

Id    Column1    Table2FK
1     1          1
2     1          2
3     1          3

I want to execute a query that aggregates over Table2.Column1 and joining it to the Table1 row with the maximum Id, but containing a non-null value for Table1.Guid. In this case it should join on row 2 of Table1. Written as a query I want something like this (although this is not valid sql):

select t2.Id, t2.Column1, t2.Table2FK, max(t1.Id), t1.Guid 
    from Table2 t2
    join Table1 t1 on t2.Table2FK = t1.Id 
    where t1.Guid is not null
    group by t2.Column1

I have managed to do the null-check and the aggregate over Table2.Id2 separately, but not both in the same query. In the first case the join returns 2 rows, in the second case it joins on row 3 of Table1.

Upvotes: 1

Views: 795

Answers (1)

Andomar
Andomar

Reputation: 238078

You can use an outer join to match rows with a non-null guid. Then row_number can give each found row a "rank", so the highest Column1 will have rank 1:

select  *
from    (
        select  row_number() over (partition by t2.Column1, 
                    order by t2.id desc) as rn
        ,       *
        from    Table2 t2
        left join    
                Table1 t1
        on      t2.Table2FK = t1.id
                and t1.guid is not null
        ) as SubQueryAlias
where   rn = 1

The subquery is required because SQL Server does not allow row_number directly in a where clause.

Upvotes: 2

Related Questions