none
Getting all records from Table A and the MAX of a column from Table B where the ID from Table A matches Table B RRS feed

  • Question

  • Basically, I have two tables. Table A has the main records and then Table B has records that identify with the records in Table A via an ID and hold various values but I just want the MAX of those values for each record in Table A.

    Table A
    -------------------
    ID: 1, Name: Row A
    ID: 2, Name: Row B
    ID: 3, Name: Row C

    Table B
    -------------------
    ID: 1, TableAID: 1, Value: 2
    ID: 2, TableAID: 1, Value: 5
    ID: 3, TableAID: 1, Value: 9
    ID: 4, TableAID: 2, Value: 1
    ID: 5, TableAID: 2, Value: 4
    ID: 6, TableAID: 3, Value: 6
    ID: 7, TableAID: 3, Value: 3
    ID: 8, TableAID: 3, Value: 8


    And the results would look like this...

    ID: 1, Name: Row A, Value: 9
    ID: 2, Name: Row B, Value: 4
    ID: 3, Name: Row C, Value: 8

    I'm trying different queries but not having any luck. Any help would be appreciated! Thanks!

    Thursday, August 8, 2019 6:32 PM

Answers

  • SELECT A.ID, A.Name, MAX(B.Value)
    FROM   TableA A
    JOIN   TableB B ON A.TableAID = A.ID
    GROUP  BY A.ID, A.Name


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Marked as answer by T Gregory Thursday, August 8, 2019 10:58 PM
    Thursday, August 8, 2019 9:24 PM
    Moderator