Comparing Similar Columns for Equality

Question

I have a (simplified) table that is structured like so:

Table: ItemData

PK | ItemID | StoreFK | Retail
1  | 100101 | 1       | 4.99
4  | 100101 | 2       | 4.99
7  | 100101 | 3       | 0.99
2  | 100102 | 1       | 6.99
5  | 100102 | 2       | 6.99
8  | 100102 | 3       | 6.99
3  | 100103 | 1       | 7.99
6  | 100103 | 2       | 8.99
9  | 100103 | 3       | 9.99

I would like to return all the items that have a different retail at one or more stores:

Returns:

ItemID
100101 
100103 

• Item 100101 has a lower retail at store 3 then at store 1 & 2 it is returned.

• Item 100103 has a different retail at each store location so it is returned.

• Item 100102 has equality in it's retail at all three stores so it are not returned.

I am not new to SQL, but I am lost as to how to make this inequality check in an efficient manor. What is the best way to check for equality in one column based on groupings on another column?

Answer 1

The easiest solution I can think of would be to just compare the average of each ItemID with the maximum (or minimum for that matter) of each ItemID

The SQL Statement would be something like

SELECT ItemID
FROM   ItemData
GROUP BY
       ItemID
HAVING MAX(Retail) <> AVG(Retail)

Note that if retail is nullable there are scenario's this method fails.

See this SQL Fiddle demo

Answer 2

With all due respect to Lieven, I prefer this:

SELECT ItemID
FROM   ItemData
GROUP BY
       ItemID
HAVING COUNT(DISTINCT Retail)>1

Answer 3

Another method: But I still love @Liven's answer :) My query tells you number of different prices as well.

select x.itemid, count(x.storefk) from (
select a.* , 
row_number() over 
(partition by a.retail order by
                   a.itemid desc) as r
from retailers a)x
group by x.itemid, x.r
having count(*) > 1
;

Results: as per OP's updated question and data.

| ITEMID | COLUMN_1 |
---------------------
| 100101 |        2 |
| 100103 |        3 |

• SQLFIDDLE DEMO

Answer 4

SELECT *
  FROM ItemData
  WHERE itemID IN ( SELECT itemID
                      FROM ItemData
                      GROUP BY itemID
                      HAVING COUNT(DISTINCT Retail) > 1
                  )