Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Comparing Rows

Author  Topic 

Bonhomme
Starting Member

3 Posts

Posted - 2006-01-31 : 17:03:57
This is a bit similar to the topic at

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=56058

but with a bit of a twist...

I have

Batch Sample Value
0 0 A
0 1 A
1 0 A
1 1 B
2 0 B
2 1 B
...


I'm looking to get something that would provide a row comparison - something that looks like


Batch Different
0 FALSE
1 TRUE
2 FALSE
...


Any suggestions for a query that would do this? Thanks!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-01-31 : 17:29:39
what is the logic of TRUE or FALSE ?
Something like this
select 	Batch, 
case when (select count(*) from tbl x where x.Batch = t.Batch) =
(select count(distinct(Value)) from tbl x where x.Batch = t.Batch) then
'TRUE'
else
'FALSE'
end as Different
from tbl t
group by Batch


----------------------------------
'KH'


Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-01-31 : 17:29:41
Given your sample data and expected result set, this works:


CREATE TABLE YourTable(Batch int, [Sample] int, Value char(1))

INSERT INTO YourTable
SELECT 0, 0, 'A' UNION ALL
SELECT 0, 1, 'A' UNION ALL
SELECT 1, 0, 'A' UNION ALL
SELECT 1, 1, 'B' UNION ALL
SELECT 2, 0, 'B' UNION ALL
SELECT 2, 1, 'B'

SELECT
Batch,
Differnt =
CASE
WHEN COUNT(Batch) = 1 THEN 'FALSE'
ELSE 'TRUE'
END
FROM
(
SELECT DISTINCT Batch, Value
FROM YourTable
) t
GROUP BY Batch

DROP TABLE YourTable


Tara Kizer
aka tduggan
Go to Top of Page

Bonhomme
Starting Member

3 Posts

Posted - 2006-02-01 : 10:22:50
Thanks for your responses everyone! I think I'm going to go with Tara's response because it's a little closer to what I was thinking, only I didn't know how to put it into SQL!

All the best -
Go to Top of Page
   

- Advertisement -