Results 1 to 1 of 1
  1. #1
    Join Date
    Nov 2005
    Posts
    11

    Unanswered: Aggregate query help

    I'm running MSSQL 2K and have a very complex nested query. In one of the query nests, I want to perform and aggregate query, such that for every Program, I recieve the lowest rate and its corresponding LTV number. I basically want the ProgramID, Rate, and LTV returned.

    Here is the query, which works but it doesnt provide the corresponding LTV:
    SELECT MIN(MD2.Rate) AS Expr1, P2.ProgramID
    FROM MatrixDetails AS MD2 INNER JOIN
    Matrix AS M2 ON MD2.MatrixID = M2.MatrixID INNER JOIN
    Programs AS P2 ON M2.MatrixID = P2.MatrixID
    GROUP BY P2.ProgramID
    6.65 10
    6.65 11
    7.15 12
    7.15 13
    6.49 16
    7.15 17
    6.25 18
    6.25 21
    6.25 23
    6.25 24
    7 25
    7 26
    7.5 27
    ...



    I want the LTV number as well, so I tried:
    SELECT MIN(MD2.Rate) AS Expr1, P2.ProgramID, MD2.LTV
    FROM MatrixDetails AS MD2 INNER JOIN
    Matrix AS M2 ON MD2.MatrixID = M2.MatrixID INNER JOIN
    Programs AS P2 ON M2.MatrixID = P2.MatrixID
    GROUP BY P2.ProgramID, MD2.LTV
    ORDER BY P2.ProgramID

    6.65 10 65
    6.85 10 70
    6.9 10 75
    6.95 10 80
    7.05 10 85
    7.5 10 90
    8.25 10 95
    6.65 11 65
    6.85 11 70
    6.9 11 75
    6.95 11 80
    7.05 11 85
    ...


    Unfortunately it didnt filter down to the lowest since it uses the lowest for every LTV and ProgramID, not just ProgramID


    *****************************EDIT
    Apparently I oversimplified the test to say i just want the min rate, but really I want to test a ton of conditions for each record within that Program, and of those lines that pass, I want the min rate, along with the identification of that lowest-rate record.

    Since the inner query only correctly identifies the minimum rate, it is not a unique identifier, and therefore all records containing matching minimum rates under that program will pass, when I need one specific record - the one that passed all of the additional conditions. I'm terrified that I would need to test all conditions in both the inner and outer query. Is this the case?
    Last edited by cragi; 01-07-06 at 00:57.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •