Max value subquery?
-
Good afternoon, struggling to come up with a way to select all the values listed when the carat_id is MAX(carat_id), which is the primary table key.
Tried a few different things w/o success, I should get a unique row per account_id, vs the dups listed in the output, think I need a subquery but have not been able to figure it out.
Any help would be greatly appreciated.
SELECT b.carat_id
,b.account_id AS 'Account ID'
,ISNULL(CONVERT(VARCHAR(10),b.FO_LOC_expiry_date,101),'N/A') AS 'LOC Expiration Date'
,ISNULL(b.work_program,0) AS 'Work Program'
,b.cy_fin_worksheet_id AS 'Current Year Financial Worksheet ID'
,ISNULL(CONVERT(VARCHAR(10),b.cy_statement_date,101),'N/A') AS 'Current Year Statement Date'
,b.cy_working_capital AS 'Current Year Working Capital'
,b.cy_wc_case AS 'Current Year Working Capital Case'
,b.cy_net_worth AS 'Current Year Net Worth'
,b.cy_nw_case AS 'Current Year Net Worth Case'
,b.cy_current_ratio AS 'Current Year Current Ratio'
,b.cy_UBWC AS 'Current Year UBWC'
,b.cy_debt_worth AS 'Current Year Debt Worth'
,b.cy_bank_debt_worth AS 'Current Year Bank Debt Worth'
,b.cy_revenue AS 'Current Year Revenue'
,b.cy_net_income AS 'Current Year Net Income'
,b.cy_np_pct AS 'Current Year Net Profit Percent'
,b.cy_LQWC AS 'Current Year LQWC'
,b.cy_UBNW AS 'Current Year UBNW'
,b.cy_cash_flow_operations AS 'Current Year Cash Flow Operations'
,b.cy_cash_flow_investments AS 'Current Year Cash Flow Investments'
,b.cy_cash_flow_financing AS 'Current Year Cash Flow Financiang'
,b.py_fin_worksheet_id AS 'Prior Year Financial Worksheet ID'
,ISNULL(CONVERT(VARCHAR(10),b.py_statement_date,101),'N/A') AS 'Prior Year Statement Date'
,ISNULL(b.py_working_capital,0) AS 'Prior Year Working Capital'
,ISNULL(b.py_wc_case,0) AS 'Prior Year Working Capital Case'
,ISNULL(b.py_net_worth,0) AS 'Prior Year Net Worth'
,ISNULL(b.py_nw_case,0) AS 'Prior Year Net Worth Case'
,ISNULL(b.py_revenue,0) AS 'Prior Year Revenue'
,b.py_net_income AS 'Prior Year Net Income'
,b.one_yr_EDF AS '1 Year EDF'
,b.five_yr_EDF AS '5 year EDF'
,b.pctile_retained_earnings_to_assets AS 'Reatained Earnings to Assets'
,b.pct_of_bond_value AS 'Percent of Bond Value'
,b.LQWC_score AS 'LQWC Score'
,b.NP_score AS 'NP Score'
,b.UBNW_score AS 'UNBW Score'
,ISNULL(b.CARAT_score,0) AS 'Carat Score'
,b.ERM_max_gross_exposure as 'Max Gross Exposure'
,b.ERM_probable_gross_exposure AS 'Probable Gross Exposure'
,b.max_issued_class_code_id AS 'Max Issued Class Code'
,b.total_bond_premium AS 'Total Bond Premium'
,b.bond_exposure_maxissuedclasscode AS 'Bond Exposure For Max Issued Class Code'
,ISNULL(c.value_desc,'N/A') AS 'Indemnity Type'
FROM CARAT b
INNER JOIN lookup c
ON b.lu_account_indemnity_type = c.column_value and c.column_name = 'lu_account_indemnity_type'
WHERE b.account_id = 464
Partial Output that shows dups
carat_idAccount IDLOC Expiration Date
898146412/31/2005
1026446412/31/2005
1157446412/31/2005
1288146412/31/2005
1419246412/31/2005
1664146402/28/2006
1790646402/28/2006
1913446412/31/2006
1968046412/31/2006
2237546412/31/2006
2412146412/31/2006
2536346412/31/2006
3004846412/31/2006
3147146412/31/2006
3289746412/31/2006
3433446412/31/2006
3577546412/31/2006
37228464N/A
38686464N/A
40161464N/A
4165946412/31/2007
4317546412/31/2007
4470246412/31/2007
4623846412/31/2007
4781746412/31/2007
4941146412/31/2007
5105146412/31/2007
5272046412/31/2007
5438746402/29/2008
5605946402/29/2008
5775146402/29/2008
5946146403/31/2008
6118346412/31/2008
6291846412/31/2008
6471446412/31/2008
6653546412/31/2008
6836246412/31/2008
6959846412/31/2008
7146146412/31/2008
7308746412/31/2008
7498146412/31/2008
8023346402/28/2009
8213146403/31/2009
8403846404/30/2009
8654346404/30/2009
8964946405/30/2009
93472464N/A
9778746402/28/2010
10305946402/28/2010
10872246402/28/2010
11465746402/28/2010
12077246402/28/2010
12722546402/28/2010
13386946402/28/2010
14391846402/28/2010
147737464N/A
153125464N/A
165164464N/A
167651464N/A
17650646403/31/2011
18154246403/31/2011
19002846403/31/2011
19671346403/31/2011
21098846403/31/2011
21709346403/31/2011
22283846403/31/2011
22873146403/31/2011
23426646403/31/2011
24188246404/30/2011
246883464N/A
25665646403/31/2012
26380546403/31/2012
27164246403/31/2012
27870946403/31/2012
28122746403/31/2012
28709246403/31/2012
-
Are you saying that you only want the data from the row that has the MAX Carat_ID for account_id = 464?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Sorry for the confusion, I added in account_id =464 in the where clause to limit the output since the data set is pretty large, figured if I could solve for the worst offender,....
I spent a good deal of time searching the forum and just found an answer from a prior post, I think here is what I have, omitted GROUP BY AND ORDER BY account_id ASC
SELECT b.carat_id
,b.account_id AS 'Account ID'
,ISNULL(CONVERT(VARCHAR(10),b.FO_LOC_expiry_date,101),'N/A') AS 'LOC Expiration Date'
,ISNULL(b.work_program,0) AS 'Work Program'
,b.cy_fin_worksheet_id AS 'Current Year Financial Worksheet ID'
,ISNULL(CONVERT(VARCHAR(10),b.cy_statement_date,101),'N/A') AS 'Current Year Statement Date'
,b.cy_working_capital AS 'Current Year Working Capital'
,b.cy_wc_case AS 'Current Year Working Capital Case'
,b.cy_net_worth AS 'Current Year Net Worth'
,b.cy_nw_case AS 'Current Year Net Worth Case'
,b.cy_current_ratio AS 'Current Year Current Ratio'
,b.cy_UBWC AS 'Current Year UBWC'
,b.cy_debt_worth AS 'Current Year Debt Worth'
,b.cy_bank_debt_worth AS 'Current Year Bank Debt Worth'
,b.cy_revenue AS 'Current Year Revenue'
,b.cy_net_income AS 'Current Year Net Income'
,b.cy_np_pct AS 'Current Year Net Profit Percent'
,b.cy_LQWC AS 'Current Year LQWC'
,b.cy_UBNW AS 'Current Year UBNW'
,b.cy_cash_flow_operations AS 'Current Year Cash Flow Operations'
,b.cy_cash_flow_investments AS 'Current Year Cash Flow Investments'
,b.cy_cash_flow_financing AS 'Current Year Cash Flow Financiang'
,b.py_fin_worksheet_id AS 'Prior Year Financial Worksheet ID'
,ISNULL(CONVERT(VARCHAR(10),b.py_statement_date,101),'N/A') AS 'Prior Year Statement Date'
,ISNULL(b.py_working_capital,0) AS 'Prior Year Working Capital'
,ISNULL(b.py_wc_case,0) AS 'Prior Year Working Capital Case'
,ISNULL(b.py_net_worth,0) AS 'Prior Year Net Worth'
,ISNULL(b.py_nw_case,0) AS 'Prior Year Net Worth Case'
,ISNULL(b.py_revenue,0) AS 'Prior Year Revenue'
,b.py_net_income AS 'Prior Year Net Income'
,b.one_yr_EDF AS '1 Year EDF'
,b.five_yr_EDF AS '5 year EDF'
,b.pctile_retained_earnings_to_assets AS 'Reatained Earnings to Assets'
,b.pct_of_bond_value AS 'Percent of Bond Value'
,b.LQWC_score AS 'LQWC Score'
,b.NP_score AS 'NP Score'
,b.UBNW_score AS 'UNBW Score'
,ISNULL(b.CARAT_score,0) AS 'Carat Score'
,b.ERM_max_gross_exposure as 'Max Gross Exposure'
,b.ERM_probable_gross_exposure AS 'Probable Gross Exposure'
,b.max_issued_class_code_id AS 'Max Issued Class Code'
,b.total_bond_premium AS 'Total Bond Premium'
,b.bond_exposure_maxissuedclasscode AS 'Bond Exposure For Max Issued Class Code'
,ISNULL(c.value_desc,'N/A') AS 'Indemnity Type'
FROM CARAT b
INNER JOIN (
SELECT account_id, MAX(carat_id) AS 'Current Carat_ID'
FROM Carat
GROUP BY account_id
)b2 ON b.carat_id = b2.carat_id AND b.account_id = b2.account_id
INNER JOIN lookup c
ON b.lu_account_indemnity_type = c.column_value and c.column_name = 'lu_account_indemnity_type'
OUTPUT
carat_idAccount IDLOC Expiration Date
89577224N/A
539973234N/A
143954399N/A
539972400N/A
539971405N/A
60566407N/A
120698412N/A
53996941308/31/2015
539968415N/A
539966416N/A
53996445705/31/2015
22429045802/28/2011
31470459N/A
539803462N/A
53996246306/30/2015
28709246403/31/2012
539950470N/A
53813747106/30/2015
188935472N/A
539949474N/A
539948475N/A
539947476N/A
53980247705/31/2015
539945478N/A
539944479N/A
539941480N/A
539800481N/A
53993948209/30/2015
53993848306/30/2015
53979948507/31/2015
53993648607/31/2015
539798487N/A
53993548806/30/2015
-
rickyschroder (1/11/2015)
...I think here is what I have, omitted GROUP BY AND ORDER BY account_id ASCThat's where I was going with this and why I asked about the Account_ID. Glad you sussed it, though. Because you spent so much time researching the problem, it's one of those things that you won't soon forget.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply