CODEWITHSUNDEEP
sqlsql-server
BlueEel
BlueEel

Reputation: 359

Select except where different in SQL

I need a bit of help with a SQL query.

Imagine I've got the following table

id  | date       | price  
1   | 1999-01-01 | 10      
2   | 1999-01-01 | 10    
3   | 2000-02-02 | 15   
4   | 2011-03-03 | 15     
5   | 2011-04-04 | 16
6   | 2011-04-04 | 20
7   | 2017-08-15 | 20

What I need is all dates where only one price is present. In this example I need to get rid of row 5 and 6 (because there is two difference prices for the same date) and either 1 or 2(because they're duplicate).

How do I do that?

Upvotes: 1

Views: 108

Answers (3)

user7715598
user7715598

Reputation: 

;WITH CTE_1(ID ,DATE,PRICE)
AS
(
SELECT 1  , '1999-01-01',10      UNION ALL
SELECT 2  , '1999-01-01',10      UNION ALL
SELECT 3  , '2000-02-02',15      UNION ALL
SELECT 4  , '2011-03-03',15      UNION ALL
SELECT 5  , '2011-04-04',16      UNION ALL
SELECT 6  , '2011-04-04',20      UNION ALL
SELECT 7  , '2017-08-15',20     
)
,CTE2
AS
(
SELECT  A.* 
FROM CTE_1 A
INNER JOIN  
 CTE_1 B 
ON A.DATE=B.DATE AND A.PRICE!=B.PRICE
)
SELECT * FROM CTE_1 WHERE ID NOT IN (SELECT ID FROM CTE2)

Upvotes: 0

rslemos
rslemos

Reputation: 2731

The following should work with any DBMS

SELECT id, date, price
FROM   TheTable o
WHERE  NOT EXISTS (
         SELECT *
         FROM TheTable i
         WHERE i.date = o.date
           AND (
             i.price <> o.price
             OR (i.price = o.price AND i.id < o.id)
           ) 
        )
;

JohnHC answer is more readable and delivers the information the OP asked for ("[...] I need all the dates [...]").

My answer, though less readable at first, is more general (allows for more complexes tie-breaking criteria) and also is capable of returning the full row (with id and price, not just date).

Upvotes: 1

JohnHC
JohnHC

Reputation: 11195

select date, 
       count(distinct price) as prices -- included to test
from MyTable
group by date
having count(distinct price) = 1 -- distinct for the duplicate pricing

Upvotes: 2

Related Questions