Microsoft SQL Server need only the highest results in a query

Question

I created the query below but there's no way I have been able to return only the highest value from the column b.new_versionnumber. Whatever is the highest value on that column, I want to return all the rows that contain that highest value.

SELECT 
    a.new_AdjustmentAmount as Adjustment_Amount,
    a.new_adjustmenttype as Adjustment_Type,
    a.new_baseprice as Base_Price,
    a.new_price as Net_Price,
    a.new_optionidid as Product,
    a.new_price as Price,
    a.new_locationid as Location_ID,
    f.smx_name as Lab_Name,
    f.smx_addressstreet1 as Address,
    f.smx_city As City,
    f.smx_stateprovince as State,
    f.smx_country as Country,
    a.new_productconfigurationid as Product_Configuration,
    a.new_quoteid as Quote_ID,
    a.new_quantity as Quantity,
    b.new_cpqstatus as Product_Configuration_Status,
    b.new_name as Product_Configuration_Name,
    b.new_quoteid as Quote_ID,
    c.new_approvalstage as Approval_Stage,
    c.new_acquisitiontype as Acquisition_Type,
    c.new_dealcolor as Deal_Color,
    c.new_financing as Finance,
    c.new_grossprofit as Gross_Profit_Percentage,
    c.new_hybridcpr as Hybrid_CPR_Type,
    c.new_leaseintrestrate as Lease_Interest_Rate,
    c.new_terms as Lease_Months,
    c.new_leaseresidual as Lease_Residual,
    c.new_leasetype as Lease_Type,
    c.new_printtype as Print_Type,
    c.new_totalinstrumentprice as Total_Instrument_Price,
    c.new_totalinterfaceallowance as Total_Interface_Allowance,
    c.new_totalreagentprice as Total_Reagent_Price,
    c.new_totalserviceprice as Total_Service_Price,
    c.new_quoteid as Record_ID,
    c.new_name as Name,
    c.new_totalamount as Net_Amount,
    c.new_acquisitiontype as Acquisition_Type,
    c.new_approvalstage as Approval_Stage,
    c.new_name as Quote_Name,
    c.new_opportunityid as Opportunity,
    c.new_printtype as Print_Type,
    c.new_totalamount as Total_Amount,
    c.new_isprimary as Is_Primary,
    c.new_opportunityid as  Opportunity_ID_Reference,
    d.closeprobability as Probability,
    d.transactioncurrencyid as Currency,
    d.customerid as Account,
    d.estimatedclosedate as Estimated_Close_Date,
    d.estimatedvalue as Estimated_Revenue,
    d.name as Name,
    d.ownerid as Account_Manager,
    d.smx_contractsoldtoaddress as Sold_To,
    d.smx_multisite as Multi_Site,
    d.statuscode as Status,
    d.totalamount as Total_Amount,
    e.smx_description as Product_Description,
    e.smx_family as Product_Family,
    e.smx_name as Name,
    e.smx_producttype as Product_Type,
    b.new_versionnumber
FROM 
    new_cpq_lineitem_tmp a
JOIN  
    new_cpq_productconfiguration b ON a.new_productconfigurationid = b.new_cpq_productconfigurationid
JOIN 
    new_cpq_quote c ON b.new_quoteid = c.new_cpq_quoteid
JOIN 
    opportunity d ON c.new_opportunityid = d.opportunityid
JOIN 
    smx_product e ON a.new_optionid = e.smx_productid
JOIN 
    smx_address f ON a.new_locationid = f.smx_addressid
WHERE 
    e.smx_producttype = '180700001'
GROUP BY 
    b.new_versionnumber,
a.new_AdjustmentAmount,
a.new_adjustmenttype,
a.new_baseprice,
a.new_price,
a.new_optionidid,
a.new_price,
a.new_locationid,
f.smx_name,
f.smx_addressstreet1,
f.smx_city,
f.smx_stateprovince,
f.smx_country,
a.new_productconfigurationid,
a.new_quoteid,
a.new_quantity,
b.new_cpqstatus,
b.new_name,
b.new_quoteid,
c.new_approvalstage,
c.new_acquisitiontype,
c.new_dealcolor,
c.new_financing,
c.new_grossprofit,
c.new_hybridcpr,
c.new_leaseintrestrate,
c.new_terms,
c.new_leaseresidual,
c.new_leasetype,
c.new_printtype,
c.new_totalinstrumentprice,
c.new_totalinterfaceallowance,
c.new_totalreagentprice,
c.new_totalserviceprice,
c.new_quoteid,
c.new_name,
c.new_totalamount,
c.new_acquisitiontype,
c.new_approvalstage,
c.new_name,
c.new_opportunityid,
c.new_printtype,
c.new_totalamount,
c.new_isprimary,
c.new_opportunityid,
d.closeprobability,
d.transactioncurrencyid,
d.customerid,
d.estimatedclosedate,
d.estimatedvalue,
d.name,
d.ownerid,
d.smx_contractsoldtoaddress,
d.smx_multisite,
d.statuscode,
d.totalamount,
e.smx_description,
e.smx_family,
e.smx_name,
e.smx_producttype

Already tried Max, Having, etc.... none worked.

you don't need to include your exact query. make it simplified, remove unnecessary fields and it short to encourage people to look into your question. — FLICKER, Aug 19 '19 at 16:33
[Bad habits to kick : using table aliases like (a, b, c) or (t1, t2, t3)](https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-table-aliases-like-a-b-c-or-t1-t2-t3) — marc_s, Aug 19 '19 at 16:54

Hugo Salaun · Answer 1 · 2019-08-21T07:03:31.793

you can try this one, it's clearly not the best optimized one but it should work and it's the easiest way to understand :

SELECT TOP 1 WITH TIES a.new_AdjustmentAmount as Adjustment_Amount,
a.new_adjustmenttype as Adjustment_Type,
a.new_baseprice as Base_Price,
a.new_price as Net_Price,
a.new_optionidid as Product,
a.new_price as Price,
a.new_locationid as Location_ID,
f.smx_name as Lab_Name,
f.smx_addressstreet1 as Address,
f.smx_city As City,
f.smx_stateprovince as State,
f.smx_country as Country,
a.new_productconfigurationid as Product_Configuration,
a.new_quoteid as Quote_ID,
a.new_quantity as Quantity,
b.new_cpqstatus as Product_Configuration_Status,
b.new_name as Product_Configuration_Name,
b.new_quoteid as Quote_ID,
c.new_approvalstage as Approval_Stage,
c.new_acquisitiontype as Acquisition_Type,
c.new_dealcolor as Deal_Color,
c.new_financing as Finance,
c.new_grossprofit as Gross_Profit_Percentage,
c.new_hybridcpr as Hybrid_CPR_Type,
c.new_leaseintrestrate as Lease_Interest_Rate,
c.new_terms as Lease_Months,
c.new_leaseresidual as Lease_Residual,
c.new_leasetype as Lease_Type,
c.new_printtype as Print_Type,
c.new_totalinstrumentprice as Total_Instrument_Price,
c.new_totalinterfaceallowance as Total_Interface_Allowance,
c.new_totalreagentprice as Total_Reagent_Price,
c.new_totalserviceprice as Total_Service_Price,
c.new_quoteid as Record_ID,
c.new_name as Name,
c.new_totalamount as Net_Amount,
c.new_acquisitiontype as Acquisition_Type,
c.new_approvalstage as Approval_Stage,
c.new_name as Quote_Name,
c.new_opportunityid as Opportunity,
c.new_printtype as Print_Type,
c.new_totalamount as Total_Amount,
c.new_isprimary as Is_Primary,
c.new_opportunityid as  Opportunity_ID_Reference,
d.closeprobability as Probability,
d.transactioncurrencyid as Currency,
d.customerid as Account,
d.estimatedclosedate as Estimated_Close_Date,
d.estimatedvalue as Estimated_Revenue,
d.name as Name,
d.ownerid as Account_Manager,
d.smx_contractsoldtoaddress as Sold_To,
d.smx_multisite as Multi_Site,
d.statuscode as Status,
d.totalamount as Total_Amount,
e.smx_description as Product_Description,
e.smx_family as Product_Family,
e.smx_name as Name,
e.smx_producttype as Product_Type,
b.new_versionnumber
FROM new_cpq_lineitem_tmp a
JOIN new_cpq_productconfiguration b ON a.new_productconfigurationid = 
b.new_cpq_productconfigurationid
JOIN new_cpq_quote c ON b.new_quoteid = c.new_cpq_quoteid
JOIN opportunity d ON c.new_opportunityid = d.opportunityid
JOIN smx_product e ON a.new_optionid = e.smx_productid
JOIN smx_address f ON a.new_locationid = f.smx_addressid
WHERE e.smx_producttype = '180700001'
GROUP BY b.new_versionnumber,
a.new_AdjustmentAmount,
a.new_adjustmenttype,
a.new_baseprice,
a.new_price,
a.new_optionidid,
a.new_price,
a.new_locationid,
f.smx_name,
f.smx_addressstreet1,
f.smx_city,
f.smx_stateprovince,
f.smx_country,
a.new_productconfigurationid,
a.new_quoteid,
a.new_quantity,
b.new_cpqstatus,
b.new_name,
b.new_quoteid,
c.new_approvalstage,
c.new_acquisitiontype,
c.new_dealcolor,
c.new_financing,
c.new_grossprofit,
c.new_hybridcpr,
c.new_leaseintrestrate,
c.new_terms,
c.new_leaseresidual,
c.new_leasetype,
c.new_printtype,
c.new_totalinstrumentprice,
c.new_totalinterfaceallowance,
c.new_totalreagentprice,
c.new_totalserviceprice,
c.new_quoteid,
c.new_name,
c.new_totalamount,
c.new_acquisitiontype,
c.new_approvalstage,
c.new_name,
c.new_opportunityid,
c.new_printtype,
c.new_totalamount,
c.new_isprimary,
c.new_opportunityid,
d.closeprobability,
d.transactioncurrencyid,
d.customerid,
d.estimatedclosedate,
d.estimatedvalue,
d.name,
d.ownerid,
d.smx_contractsoldtoaddress,
d.smx_multisite,
d.statuscode,
d.totalamount,
e.smx_description,
e.smx_family,
e.smx_name,
e.smx_producttype
ORDER BY b.new_versionnumber DESC

You just order by b.new_versionnumber in descending and then you take only the first row. It's not efficient because you have to get the entire result and then keep one line but it should works...

I hope it helps. :-)

Thank you. Selecting top 1 will not help though, as I may have 5, 6 rows with the same max value on b.new_versionnumber column.... — snowman, Aug 20 '19 at 20:01
I added WITH TIES clause in my answer so it should be OK with multiple rows having the same value. Let me know if it's working. — Hugo Salaun, Aug 21 '19 at 07:04
The problem is that this is bringing only the result from one quote. I need to bring the highest version number from every single quote... so one quote may have 5 versions, I want to bring the fifth. But another quote may have 26 versions, so I want to bring the 26th of that one. Another one has 13, I want to bring the 13th version..... right now it's bringing only the highest from the whole database..... — snowman, Aug 22 '19 at 15:47

score 1 · Answer 2 · answered Aug 19 '19 at 16:46

1

You need to find the maximum version number in a sub-query an include that in your joins. Like this...

INNER JOIN (
               SELECT MAX(new_versionnumber) AS max_new_versionnumber
               FROM   new_cpq_productconfiguration
           ) AS x ON b.new_versionnumber = x.max_new_versionnumber

answered Aug 19 '19 at 16:46

Isaac

3,240
2
24
31

Hi, thank you. I tried to include your suggestion to my query but was never able to make it run successfully. – snowman Aug 20 '19 at 20:02
Are you getting an error? What doesn't work? Also, as FLICKER suggested a simplified example along with sample data would help us help you. – Isaac Aug 20 '19 at 21:56

score 1 · Answer 3 · answered Aug 19 '19 at 17:49

1

Find the columns in new_cpq_productconfiguration table that has to be group by or partition by, and that result can fit your needs. i.e.

JOIN
(select new_productconfigurationid, AdditionalColumn, max (new_versionnumber) as new_versionnumber
 from new_cpq_productconfiguration
 group by new_productconfigurationid, AdditionalColumn,
) as b ON a.new_productconfigurationid = b.new_cpq_productconfigurationid

or

JOIN
(  select new_productconfigurationid, AdditionalColumn, 
          ROW_NUMBER () OVER (PARTITION BY DesiredColumns order by new_versionnumber desc) as RN
  from    new_cpq_productconfiguration
) as b ON a.new_productconfigurationid = b.new_cpq_productconfigurationid and b.RN = 1

answered Aug 19 '19 at 17:49

Shekar Kola

1,287
9
15

Hi, thank you. I tried to include your two suggestions to my query but was never able to make it run successfully. – snowman Aug 20 '19 at 20:02
Hello @snowman, as i mentioned in my answer you need to identify columns (you may need to add additional column based on table (`new_cpq_productconfiguration`) structure ). can you post the result set of the query and expected result so that it would be easy to understand your scenario. – Shekar Kola Aug 21 '19 at 04:09

score 1 · Answer 4 · answered Aug 20 '19 at 09:36

First of all, since you are not performing any aggregations in your SELECT part, you may just remove GROUP BY part. If you've used it to avoid duplicates, just use distinct for that. Second, add and b.new_versionnumber = (select top 1 new_versionnumber from new_cpq_productconfiguration order by new_versionnumber desc)

to your WHERE filter

Final query is below

    SELECT distinct
    a.new_AdjustmentAmount as Adjustment_Amount,
    a.new_adjustmenttype as Adjustment_Type,
    a.new_baseprice as Base_Price,
    a.new_price as Net_Price,
    a.new_optionidid as Product,
    a.new_price as Price,
    a.new_locationid as Location_ID,
    f.smx_name as Lab_Name,
    f.smx_addressstreet1 as Address,
    f.smx_city As City,
    f.smx_stateprovince as State,
    f.smx_country as Country,
    a.new_productconfigurationid as Product_Configuration,
    a.new_quoteid as Quote_ID,
    a.new_quantity as Quantity,
    b.new_cpqstatus as Product_Configuration_Status,
    b.new_name as Product_Configuration_Name,
    b.new_quoteid as Quote_ID,
    c.new_approvalstage as Approval_Stage,
    c.new_acquisitiontype as Acquisition_Type,
    c.new_dealcolor as Deal_Color,
    c.new_financing as Finance,
    c.new_grossprofit as Gross_Profit_Percentage,
    c.new_hybridcpr as Hybrid_CPR_Type,
    c.new_leaseintrestrate as Lease_Interest_Rate,
    c.new_terms as Lease_Months,
    c.new_leaseresidual as Lease_Residual,
    c.new_leasetype as Lease_Type,
    c.new_printtype as Print_Type,
    c.new_totalinstrumentprice as Total_Instrument_Price,
    c.new_totalinterfaceallowance as Total_Interface_Allowance,
    c.new_totalreagentprice as Total_Reagent_Price,
    c.new_totalserviceprice as Total_Service_Price,
    c.new_quoteid as Record_ID,
    c.new_name as Name,
    c.new_totalamount as Net_Amount,
    c.new_acquisitiontype as Acquisition_Type,
    c.new_approvalstage as Approval_Stage,
    c.new_name as Quote_Name,
    c.new_opportunityid as Opportunity,
    c.new_printtype as Print_Type,
    c.new_totalamount as Total_Amount,
    c.new_isprimary as Is_Primary,
    c.new_opportunityid as  Opportunity_ID_Reference,
    d.closeprobability as Probability,
    d.transactioncurrencyid as Currency,
    d.customerid as Account,
    d.estimatedclosedate as Estimated_Close_Date,
    d.estimatedvalue as Estimated_Revenue,
    d.name as Name,
    d.ownerid as Account_Manager,
    d.smx_contractsoldtoaddress as Sold_To,
    d.smx_multisite as Multi_Site,
    d.statuscode as Status,
    d.totalamount as Total_Amount,
    e.smx_description as Product_Description,
    e.smx_family as Product_Family,
    e.smx_name as Name,
    e.smx_producttype as Product_Type,
    b.new_versionnumber
FROM 
    new_cpq_lineitem_tmp a
JOIN  
    new_cpq_productconfiguration b ON a.new_productconfigurationid = b.new_cpq_productconfigurationid
JOIN 
    new_cpq_quote c ON b.new_quoteid = c.new_cpq_quoteid
JOIN 
    opportunity d ON c.new_opportunityid = d.opportunityid
JOIN 
    smx_product e ON a.new_optionid = e.smx_productid
JOIN 
    smx_address f ON a.new_locationid = f.smx_addressid
WHERE 
    e.smx_producttype = '180700001'
    and b.new_versionnumber = (select top 1 new_versionnumber
                                from new_cpq_productconfiguration
                                order by new_versionnumber desc)

Thank you. Selecting top 1 will not help though, as I may have 5, 6 rows with the same max value on b.new_versionnumber column.... — snowman, Aug 20 '19 at 20:01
Sorry, but I think you did not get it completely. Top 1 gives you the highest version number value, but if you have more than 1 row with it - all of them will be returned. Just try it out — Dmitriy Bayraktar, Aug 21 '19 at 06:42

Microsoft SQL Server need only the highest results in a query

4 Answers4