How to join to table row with max column value in a View

  • I have a View defined as:

    SELECT dbo.TabA.Col1, dbo.TabB.Col2

    FROM dbo.TabA LEFT OUTER JOIN

    dbo.TabB ON dbo.TabB.ForgnKey = dbo.TabA.PrimKey

    In this case, I got all of TabB col2 on the View result. However, I only want the View to display the row of TabB where its Col2 value is max. (obviously, I don't want to display the rows with NULL).

    How can I do this? I tried using the MAX function but it won't work. Plesae help. Thanks in advance.

    sg2000

  • Perhaps something like:

    declare @tabB table (Col1 int, Col2 int)

    insert into @tabB

    select 1, 4 union all

    select 2, 9 union all

    select 3, 8 union all

    select 4, 9

    select * from @tabB

    where col2 = (select max(col2) from @tabB)

    /* -------- Sample Output: --------

    Col1 Col2

    ----------- -----------

    2 9

    4 9

    */

  • sg2000 (1/10/2008)


    I have a View defined as:

    SELECT dbo.TabA.Col1, dbo.TabB.Col2

    FROM dbo.TabA LEFT OUTER JOIN

    dbo.TabB ON dbo.TabB.ForgnKey = dbo.TabA.PrimKey

    In this case, I got all of TabB col2 on the View result. However, I only want the View to display the row of TabB where its Col2 value is max. (obviously, I don't want to display the rows with NULL).

    How can I do this? I tried using the MAX function but it won't work. Plesae help. Thanks in advance.

    sg2000

    SELECT dbo.TabA.Col1, dbo.TabB.Col2

    FROM dbo.TabA

    LEFT JOIN dbo.TabB

    ON dbo.TabB.ForeignKey = dbo.TabA.PrimaryKey

    AND dbo.TabB.Col2 = (SELECT TOP 1 t.Col2

    FROM dbo.TabB t

    WHERE t.ForeignKey = dbo.TabB.ForeignKey

    ORDER BY t3.Col DESC)

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • Grant:

    Thanks very much for the quick response. Yes, it works!!!! I just wonder why TOP 1 works and not MAX. Well, may be I can find out later on. Thanks again.

    sg2000

  • Kent:

    Thanks for the quick response. However, I can't figure out how to fit what you recommend in a View? Anyway, I got it working by following the suggestion from Grant (the next poster).

    sg2000

  • Depending on the data type & how you write the query, the MAX should work too. I've just found that the TOP... ORDER BY works better especially with a clustered index.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • Thanks Grant for the info.

    sg2000

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply