Help with Resultset

[Paul Ilacqua]

I'm using SQL Server 2008 and am looking to create a resultset with the
differences in the "Field_Value" column for each AssyID.
There are 50 or so AssyID's and some "Field_Value's" are identical assembly
to assembly. Field_Index values are always the same. The values I want in
the resultset is the values that are identical and which ones are different.

IE AssyID #1 has the same data as AssyID #10.

AssyID Field_Index Field_Value
------ ----------- -----------
1 1 460
1 2 3
1 3 60
1 4 280
1 5 80
1 6 81.5
1 7 3500
1 8 380 - 420
1 9 3
1 10 50
1 11 280
1 12 80
1 13 67.6
1 14 2900
1 15 IP 54
1 16 37802877
1 17 MODEL
1 18 SERIAL
2 1 460
2 2 3
2 3 60
2 4 280
2 5 63
2 6 81.5
2 7 3500
2 8 380 - 420
2 9 3
2 10 50
2 11 280
2 12 63
2 13 67.6
2 14 2900
2 15 IP 54
2 16 37802877
2 17 MODEL
2 18 SERIAL

[Jeroen Mostert]

Paul Ilacqua wrote:
> I'm using SQL Server 2008 and am looking to create a resultset with the
> differences in the "Field_Value" column for each AssyID.
> There are 50 or so AssyID's and some "Field_Value's" are identical
> assembly to assembly. Field_Index values are always the same. The values
> I want in the resultset is the values that are identical and which ones
> are different.
>
> IE AssyID #1 has the same data as AssyID #10.
>

This is a good one. Thinking in sets, a set contains all equal values if its
minimum value is equal to its maximum value (ignoring the possibility of
NULL values here). So the first part (values that are the same across, eh,
Assys), is easy:

SELECT Field_Index, Single_Value = MIN(Field_Value)
FROM Assys
GROUP BY Field_Index
HAVING MIN(Field_Value) = MAX(Field_Value)

The second part (values that are not the same) is now also easy because it's
the complement of the first one:

SELECT AssyID, Field_Index, Field_Value
FROM Assys
WHERE Field_Index NOT IN (
SELECT Field_Index
FROM Assys
GROUP BY Field_Index
HAVING MIN(Field_Value) = MAX(Field_Value)
)

How you want to combine these in the result set and how you want to involve
AssyID isn't clear to me. Something like grouping on AssyID by "uniqueness"
of its fields gets complicated quickly. Work out exactly what you're looking
for first, with an example input and output result set. This may also clue
you in to the query you need.

--
J.

[Paul Ilacqua]

Thanks J
I appreciate the "kick start". I wrote a vb solution and will compare the
results.
Paul
"Jeroen Mostert" <jmos...@xs4all.nl> wrote in message
news:4b0649c7$0$22934$e4fe...@news.xs4all.nl...