1

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

  • 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. – Rodney G Nov 18 '14 at 17:35
  • Requirements are not clear. – ypercubeᵀᴹ Nov 18 '14 at 19:08
  • Requirements are pretty clear now, @ypercube ;) wanna give it a go? – Rodney G Nov 18 '14 at 22:39
  • @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. – Pavel Nefyodov Nov 19 '14 at 10:28
0

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
  • In your first and second example, the error "Subquery returned more than 1 value" is returned. – Rodney G Nov 18 '14 at 17:55
  • Please see the update. – Pavel Nefyodov Nov 18 '14 at 19:03
  • 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 Nov 18 '14 at 19:21
  • 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 – Rodney G Nov 19 '14 at 19:41
  • 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? – Pavel Nefyodov Nov 19 '14 at 23:32
1

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
  • 1
    Did you mean "ORDER BY Price1, Price2 ASC"? Otherwise you get maximum not minimum prices? – Pavel Nefyodov Nov 18 '14 at 9:31
  • Yes you're right. Should be Ascending. – DBNull Nov 18 '14 at 12:24
  • 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! – Rodney G Nov 18 '14 at 17:23
0

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)
  • Paste the Union of your tables is place of MyTable. – user2766490 Nov 18 '14 at 10:13
  • What if MIN of Price1 and MIN of Price2 in the different rows? Your request will return an empty set. – Pavel Nefyodov Nov 18 '14 at 10:40
  • Paste "OR" instead of "AND" in WHERE clause – user2766490 Nov 18 '14 at 10:49
  • 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. – Rodney G Nov 18 '14 at 17:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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