CODEWITHSUNDEEP
sqlequalityinequality
recursion.ninja
recursion.ninja

Reputation: 5488

Comparing Similar Columns for Equality

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

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?

Upvotes: 2

Views: 177

Answers (4)

Bulat
Bulat

Reputation: 6979

With all due respect to Lieven, I prefer this:

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

Upvotes: 1

Lieven Keersmaekers
Lieven Keersmaekers

Reputation: 58491

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

Upvotes: 5

bonCodigo
bonCodigo

Reputation: 14361

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 |

Upvotes: 2

Glenn
Glenn

Reputation: 9170

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

Upvotes: 1

Related Questions