Below is a query I'm using to extract the best Price1 amongst several distributor's pricing tables.

    vProducts_PriceQty_Union.DBID AS PricingDBID, 
    products ON 
        vProducts_PriceQty_Union.UPC = products.UPC 
        MIN(Price1) AS Price1, 
        SUM(Quantity) AS Quantity, 
        COUNT(DBID) AS VendorCount
    ) AS T2 ON 
        T2.UPC = vProducts_PriceQty_Union.UPC 
        vProducts_PriceQty_Union.Price1 = T2.Price1 
    vProducts_PriceQty_Union.UPC = '082442818986'

"vProducts_PriceQty_Union" is a view with a UNION ALL of three tables, which looks like this:

SELECT     UPC, Price1, Price2, Quantity, DBID FROM Table1
SELECT     UPC, Price1, Price2, Quantity, DBID FROM Table2
SELECT     UPC, Price1, Price2, Quantity, DBID FROM Table3


  1. I realize column name "DBID" is a reserved name, but it's too late in the game to change, and does not seem to affect any query results in an adverse way
  2. DBID is simply a numeric reference to a table containing the data.
  3. There are no duplicates of UPCs within any of the tables referenced in the union query, only duplicate UPCs when they are unioned
  4. The WHERE clause is only shown in the first query to return example results and not used in the production query

Normally, the first query shown above returns one row for each UPC, showing the lowest Price1 from the three available. However, in cases where Price1 is the same amongst more than one table, the query returns more than one row for a given UPC, as follows:

UPC             Price1      Price2      Quantity    Pricing DBID    VendorCount
082442818986    597.0000    569.0000    103.00      1               2
082442818986    597.0000    569.0000    103.00      2               2

My question is:

How do I list only one row for this type of instance (more than one returned), with a preference of using PricingDBID = 1 (the main store, not an offsite warehouse), when one of the results contains PricingDBID = 1. If one of the records does NOT contain PricingDBID = 1, then select only the first one (or random, if you feel up to it) that is returned (all PricingDBIDs above 1 are offsite warehouses and we don't care which one gets chosen, just choose ONE).

I've tried this with CASE statements, but cannot for the life of me figure it out. I suspect it has something to do with the "T2.UPC = Products_PriceQty_Union.UPC AND vProducts_PriceQty_Union.Price1 = T2.Price1" bindings, but not exactly sure what the syntax might be to accomplish this. Over 20 hours on this and no resolution so far, so I'm reaching out to the community. Any help is greatly appreciated!

A follow up question...:

Not sure if this is where this supplemental question should go. If this should be posted as new question, let me know (I'm new to this group as far as posting goes).

When I attempt to paste the working query (see ypercube's answer) into view designer in SSMS and attempt to view the results (ctrl+r), I get "unhandled win32 exception occurred in sqlwb.exe [14240]". Then, when I debug, I'm presented with the dialog "A buffer overrun has occurred in SqlWb.exe which has corrupted the programs's internal state. Press Break to debug". On pressing "Break", the offending line is "0x7813BD93 = 2014559635 and dword ptr ds:[781C4920h],0" (if that means anything to anyone). On stopping code in Visual Studio debugger SSMS closes and I have to start over. I've searched far and wide for a solution to this. However, when I run the query in the query window (not builder), the query runs just fine!

A workaround is to script the creation of the view using Create view, which works just fine for querying. As long as I don't want to edit the query in the view designer, it works just fine. Weird.

Does anyone have any input or experience with this, where queries pasted into view designer cause the program to crash? Ah, Microsoft, it's always something with you...

  • What DBMS do you use? Commented Apr 9, 2015 at 5:29
  • I assumed it is Oracle (from the DBID), edited tag. Please re-edit if that is wrong. Commented Apr 9, 2015 at 12:28
  • Why is there a join to products? Seems the table is not used anywhere, unless it is restricting the final rows somehow. Commented Apr 9, 2015 at 12:30
  • Not 100% sure but DBID is not reserved. That's why you have no issues using it as a column name. Commented Apr 9, 2015 at 12:47
  • In case anyone is wondering, UPC 082442818986 is a Beretta 9mm... Commented Apr 9, 2015 at 13:04

1 Answer 1


I think this will get what you want. It may not be the best regarding efficiency (due to the unioned view):

WITH LowestPrices AS
    ( SELECT 
          SUM(Quantity) OVER (PARTITION BY UPC) AS Quantity, 
          DBID AS PricingDBID, 
          COUNT(*) OVER (PARTITION BY UPC) AS VendorCount,
                             ORDER BY Price1,
                                      DBID         -- to resolve ties
                            ) AS Rn
     -- WHERE
     --     UPC = '082442818986'
    Rn = 1
    UPC ;

If the join to Products is really needed, you can add it, either inside the CTE or in the external query.

  • To clarify for some folks' questions/comments after my initial post: using SQL Server 2005 @colin, we all have to make a living. Don't judge. :p ypercube, that looks like it should work! I need to leave for a while but I want to check this after I'm back. To clarify, the products table is necessary as it contains a bunch of other info necessary for return to the web client. I excluded all those other fields to simplify the query. Thanks again, can't wait to try it!
    – Rodney G
    Commented Apr 9, 2015 at 20:32
  • @yoercube you're the man! (assuming you're a man) Works like a million bucks! Thanks so much for your help. I'm quite certain I've used your recommendations from other answers you've provided on this site many times. You're a great asset! Cheers! Super happy to move forward with this project.
    – Rodney G
    Commented Apr 10, 2015 at 15:16
  • @RodneyG thnx. Please edit the question and change the tag from Oracle to SQL-Server, if that is what you use (for this question.) Commented Apr 10, 2015 at 15:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.