L
LGC
I have the following query:
SELECT CustID, ProdID, Sum(Amt) AS Prem
FROM tbl_dta_DivYearTran
GROUP BY CustID, ProdID
ORDER BY CustID, Sum(Amt) DESC;
Sample Output:
qry_sys_CustProd CustID ProdID Prem
BLUSAVE 5 $22,027.00
BOONBAN 2 $19,663.08
BREDSBK 4 $9,791.00
BRITFIR 5 $14,960.00
BRITFIR 4 $2,694.82
BROOPAT 5 $17,613.00
BROOPOW 5 $8,593.00
BRUNFSB 4 $28,936.00
This results in a data set of three columns: CustID (customer), ProdID
(producer), and Prem (premium). Almost all of the rows are unique except
for a few where there are two ProdID values for one CustID. I need to build
a data set containing unique rows of CustID and ProdID using the ProdID
where Prem is the greatest.
Here is my attempt:
SELECT CustID, First(ProdID) AS ProdID
FROM qry_sys_CustProd
GROUP BY CustID;
Sample Output:
Query1 CustID ProdID
BLUSAVE 5
BOONBAN 2
BREDSBK 4
BRITFIR 4
BROOPAT 5
BROOPOW 5
BRUNFSB 4
This does not work, though, as grouping by CustID in the 2nd query
apparently disrupts the order of the records from the first query. Notice
that the producer for BRITFIR is 4, not 5, which is the one with greatest
premium from the first query.
Any help is appreciated. Thanks.
LGC
SELECT CustID, ProdID, Sum(Amt) AS Prem
FROM tbl_dta_DivYearTran
GROUP BY CustID, ProdID
ORDER BY CustID, Sum(Amt) DESC;
Sample Output:
qry_sys_CustProd CustID ProdID Prem
BLUSAVE 5 $22,027.00
BOONBAN 2 $19,663.08
BREDSBK 4 $9,791.00
BRITFIR 5 $14,960.00
BRITFIR 4 $2,694.82
BROOPAT 5 $17,613.00
BROOPOW 5 $8,593.00
BRUNFSB 4 $28,936.00
This results in a data set of three columns: CustID (customer), ProdID
(producer), and Prem (premium). Almost all of the rows are unique except
for a few where there are two ProdID values for one CustID. I need to build
a data set containing unique rows of CustID and ProdID using the ProdID
where Prem is the greatest.
Here is my attempt:
SELECT CustID, First(ProdID) AS ProdID
FROM qry_sys_CustProd
GROUP BY CustID;
Sample Output:
Query1 CustID ProdID
BLUSAVE 5
BOONBAN 2
BREDSBK 4
BRITFIR 4
BROOPAT 5
BROOPOW 5
BRUNFSB 4
This does not work, though, as grouping by CustID in the 2nd query
apparently disrupts the order of the records from the first query. Notice
that the producer for BRITFIR is 4, not 5, which is the one with greatest
premium from the first query.
Any help is appreciated. Thanks.
LGC