SQL for Smarties | SQL Programming Style | Trees and Hierarchies in SQL | SQL Puzzles and Answers | Data and Databases


Monday, May 22, 2006

need help with a query

SQL Apprentice Question
Create table test
(col1 varchar(50),col2 int)

insert test values ('ABC',1)
insert test values ('ABC',2)
insert test values ('XYZ',1)
insert test values ('XYZ',1)


I would like the output to report those unique values in Col1 that have
different values in Col2.. So the output based upon the input in table test
would return
'ABC'



Celko Answers
SELECT col1
FROM CrappyNonTableWithoutKey
GROUP BY col1
HAVING MIN(col2) < MAX(col2) ;

SQL Apprentice Question
Should that not be

HAVING MIN(col2) <> MAX(col2) ;

They are looking for values where col2 are different.


Celko Answers
Think about it for a second:

HAVING MIN(col2) <> MAX(col2) ;


is defined as:


HAVING (MIN(col2) < MAX(col2)) OR (MIN(col2) > MAX(col2))


becomes


HAVING (MIN(col2) < MAX(col2)) OR FALSE


becomes


HAVING (MIN(col2) < MAX(col2))

No comments: