aggregate query help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

aggregate query help

Author

Topic

cragi
Starting Member

14 Posts

Posted - 2006-01-07 : 01:08:53

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
WHERE MD2.TEST='54'

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

cragi
Starting Member

14 Posts

Posted - 2006-01-07 : 01:13:28

was trying to edit it before posting but it sent.. basically there are a ton of conditions being tested in the where clause and subclauses that I'm excluding to simplify the issue. I would like to retrieve not just the minimum value, but also all of the contents of the record containing that minimum value. If I used a nested query to look up just the minimum value, I would need to retest all of the conditions, which I'm hoping to avoid....

Kristen
Test

22859 Posts

Posted - 2006-01-07 : 06:34:03

"was trying to edit it before posting but it sent"

On posts that you have Authored there is a little icon to let you re-edit them after posting ... if that helps!

This will select the LTV for the "MIN" row in your aggregate. The code in red is to provide a single (I've chosen MIN) value for LTV if Rate + ProgramID is NOT unique in MatrixDetails


SELECT T.Expr1,
       T.ProgramID,
       MIN(MD3.LTV)
FROM
(
    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 
    WHERE MD2.TEST='54'
    GROUP BY P2.ProgramID
) T
    JOIN MatrixDetails AS MD3
        ON MD3.Rate = T.Expr1
GROUP BY T.Expr1, T.ProgramID

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources