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?

What would you do if `retail` in all three stores are different? — peterm, Jan 11 '13 at 21:53
Edited the OP to give an example of that case. Expected/desired behavior would be to return that item. — recursion.ninja, Jan 11 '13 at 21:57

Lieven Keersmaekers · Answer 1 · 2013-01-11T22:25:43.953

5

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

edited Jan 11 '13 at 22:25

answered Jan 11 '13 at 21:59

Lieven Keersmaekers

57,207
13
112
146

`Store` is not a table... can you select `FROM` a Column? – recursion.ninja Jan 11 '13 at 22:01
@awashburn - You didn't tell us the table name so I used `Store` as it made sense to me. Just replace it with your actual tablename. – Lieven Keersmaekers Jan 11 '13 at 22:02
@LievenKeersmaekers I think your `WHERE` needs to be a `HAVING`. But nice solution. – MikeSmithDev Jan 11 '13 at 22:05
@MikeSmithDev - thx for catching that. I've updated the answer. – Lieven Keersmaekers Jan 11 '13 at 22:10
@LievenKeersmaekers +1 it's simple and **syntatically sugary**. – bonCodigo Jan 11 '13 at 22:16

bonCodigo · Answer 2 · 2013-01-11T22:21:23.550

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

Glenn · Answer 3 · 2013-01-11T22:06:34.183

1

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

edited Jan 11 '13 at 22:06

answered Jan 11 '13 at 22:01

Glenn

8,932
2
41
54

score 1 · Accepted Answer · edited Jan 11 '13 at 23:22

1

With all due respect to Lieven, I prefer this:

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

edited Jan 11 '13 at 23:22

recursion.ninja

5,377
7
46
78

answered Jan 11 '13 at 22:55

Bulat

6,869
1
29
52

Though all answers worked with the sample data provided in OP, this was the *only* answer that returned the desired results with my **actual** database data. – recursion.ninja Jan 11 '13 at 23:25
I am not sure why this would give different result from Lieven's answer. I just put it because it is faster. If results are indeed different, that fact deserves a separate question. – Bulat Jan 13 '13 at 01:16

Comparing Similar Columns for Equality

4 Answers4

Linked