CODEWITHSUNDEEP
sqlsybase
userx
userx

Reputation: 3773

Select statement to find variable data in a column

Table name : - Trade

TradeNumber       spread
1                 .1
1                 .1
2                 .3
2                 .4

I want to get all the trades where spread varies. That is my result set should contain only trade number 2.

The query I could construct is 

select TradeNumber from Trade t where t.spread <> (select t.spread from TradeNumber)

Didn't get a chance to run it. Is there an aggregating/ compound function to solve it ?

Upvotes: 1

Views: 304

Answers (4)

fancyPants
fancyPants

Reputation: 51918

Just for completeness, another solution.

SELECT TradeNumber FROM (
SELECT
TradeNumber
FROM
Trade
GROUP BY TradeNumber, spread
) sq
GROUP BY TradeNumber
HAVING COUNT(*) >1

Upvotes: 1

Bastien Jansen
Bastien Jansen

Reputation: 8846

I would go with

SELECT
TradeNumber
FROM
Trade
GROUP BY TradeNumber
HAVING count(distinct spread) > 1

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270503

The least expensive way to do this is:

select tradenumber
from t
group by tradenumber
having min(spread) <> max(spread)

Using min and max users fewer resources than count(distinct).

Upvotes: 2

Rapha&#235;l Althaus
Rapha&#235;l Althaus

Reputation: 60503

SELECT DISTINCT t.TradeNumber
FROM Trade t
WHERE EXISTS (SELECT NULL
                  FROM Trade t1
                  WHERE t.TradeNumber = t1.tradeNumber
                  AND t.spread <> t1.spread)

or

select TradeNumber
FROM Trade
GROUP BY TradeNumber
HAVING COUNT(DISTINCT spread) > 1

sqlFiddle

Upvotes: 4

Related Questions