Select only rows with minimum values from union table

Question

SQL Server 2005

Here is a sampling of the data from a union of two tables (named "vProducts_All"):

DBID UPC Price1 Price2
1 023614025559 689.0000 649.0000
2 023614025559 632.0000 602.0000
1 011457891333 252.0000 232.0000
2 011457891333 284.0000 254.0000
1 123451234512 199.0000 159.0000
2 123451234512 195.0000 162.0000
I want to return only the rows showing the minimum for Price1 field, IN ADDITION TO all of the other records that didn't have duplication, something like:
DBID UPC Price1 Price2
2 023614025559 632.0000 602.0000
1 011457891333 252.0000 232.0000
2 123451234512 195.0000 162.0000
+ All the other records that didn't have duplication in the UPC field

Have tried in vain using joins to derived tables, but nothing works. Any help appreciated

• Rodney G: Wanted to further explain the data... There are thousands of records returned in a view called vProducts_All. In that view is the UNION statement, combining two or more tables. With this view, there might be records with matching UPCs. I want my query to return all of the records, EXCEPT the duplicates, and then in the case of identical UPCs, I only want to return only the rows with the lowest Price1 and Price2. Hope that clarifies a bit.
• ypercubeᵀᴹ: Requirements are not clear.
• Rodney G: Requirements are pretty clear now, @ypercube ;) wanna give it a go?
• Pavel Nefyodov: @ypercube makes a very valid point. In your example for UPC 123451234512 should it be Price2 159.0000 not 162.0000? Another thing is: there is no guarantee that minimums for Price1 and Price2 for the same UPC will belong to the same DBID.

Answer 1

Granted DBID and UPC are not guaranteed to be the same for Price1 and Price2 your code should be similar to this:

SELECT
(SELECT TOP 1 DBID FROM sub WHERE Price1=(SELECT MIN(Price1) FROM sub)) AS DBID1
,(SELECT TOP 1 UPC FROM sub WHERE Price1=(SELECT MIN(Price1) FROM sub)) AS UPC1
,(SELECT MIN(Price1) FROM sub) as Price1
,(SELECT TOP 1 DBID FROM sub WHERE Price2=(SELECT MIN(Price2) FROM sub)) AS DBID2
,(SELECT TOP 1 UPC FROM sub WHERE Price2=(SELECT MIN(Price2) FROM sub)) AS UPC2
,(SELECT MIN(Price2) FROM sub) AS Price2

where sub is a result of a union of two tables.

Update: You can make the query a bit faster by reducing the number of Aggregates:

SELECT
 (SELECT TOP 1 DBID FROM sub WHERE Price1=a1.Price1) AS DBID1
, (SELECT TOP 1 UPC FROM sub WHERE Price1=a1.Price1) AS UPC1
, Price1
, (SELECT TOP 1 DBID FROM sub WHERE Price2=a1.Price2) AS DBID2
, (SELECT TOP 1 UPC FROM sub WHERE Price2=a1.Price2) AS UPC2
, Price2 
FROM 
(
SELECT (SELECT MIN(Price1) FROM sub) as Price1
,(SELECT MIN(Price2) FROM sub) AS Price2
) a1

Update2: I can only assume that - you want mimimums within same UPC. You can have same minimum values for different [DBID]s. With this in mind the following code is produced. Please note that [DBID]s produced randomly in case when there are ties.

SELECT 
a1.Price1
, (SELECT TOP 1 [DBID] FROM sub WHERE Price1=a1.Price1 and UPC=a1.UPC) AS [DBID]
, a1.Price2
, (SELECT TOP 1 [DBID] FROM sub WHERE Price2=a1.Price2 and UPC=a1.UPC) AS [DBID]
, a1.UPC
FROM (SELECT MIN(Price1) AS Price1, MIN(Price2) AS Price2, UPC FROM sub group by UPC) a1

• Rodney G: In your first and second example, the error "Subquery returned more than 1 value" is returned.
• Pavel Nefyodov: Please see the update.
• Rodney G: The subquery error has now gone away (thanks for the edit), but the returned recordset consists of only one record as such: 1 754908550104 2.0000 1 762344001166 0.0000, when I would expect many results, and as you can see, the two UPCs returned do not match
• Rodney G: It works! Thanks much @pavel ! However (and there is always a however ;), the runtime for that query against my 5500 records takes 2.6 seconds! This won't do for a product lookup web page that gets hit thousands of times per day. Would you recommend I schedule a job to run this query and write it to another table for faster lookups? Or can you recommend a better way to run this query? Many thanks for this answer! You've saved my sanity :p
• Pavel Nefyodov: The main query cost contributor is SELECT MIN(Price1) AS Price1, MIN(Price2) AS Price2, UPC FROM sub group by UPC. I don't think it can be improved as that is your business requirement. Yes, writing the results of your query into another table with the properly chosen index is definitely an option. If you happy with the query would be it be possible to upvote and accept as an answer?

Answer 2

You can treat the union as a subquery.

SELECT TOP 1 DBID, UPC, Price1, Price2
FROM (SELECT DBID, UPC, Price1, Price2 
 FROM Table1
 UNION 
 SELECT DBID, UPC, Price1, Price2 
 FROM Table2) sub 
ORDER BY Price1 DESC

• Pavel Nefyodov: Did you mean "ORDER BY Price1, Price2 ASC"? Otherwise you get maximum not minimum prices?
• DBNull: Yes you're right. Should be Ascending.
• Rodney G: That suggesting ends up with only one record returned: 1 000998622200 0.0000 0.0000, returning the first record it sees with the lowest price (0ドル). Gonna try the other's recommendations now... thanks for the input!

Answer 3

Where minimum is Price2 or Price1? If both of them then

SELECT DBID,UPC,Price1,Price2 
FROM MyTable 
WHERE Price2 = (SELECT MIN(Price2) FROM MyTable)
 AND Price1 = (SELECT MIN(price1) FROM MyTable)

• user2766490: Paste the Union of your tables is place of MyTable.
• Pavel Nefyodov: What if MIN of Price1 and MIN of Price2 in the different rows? Your request will return an empty set.
• user2766490: Paste "OR" instead of "AND" in WHERE clause
• Rodney G: In this case (using AND in the WHERE clause), the results have only two records (out of thousands), showing only the first two that matched the minimum price. Using OR instead (A very SLOW query) results in many hundreds of matches, but these are not for any of the records that have matching UPCs.