Forum Discussion
Return all fields in results
Hello. This SQL finds all records that the same in Field1 but are different in Field2. It works fine but only returns the Field1 and Field 2 in the results. I would like all 6 Fields of my r...
I’m not sure if this will work, didn't have data to test, you can try SQL below
WITH T AS
(
SELECT ROW_NUMBER() OVER( ORDER BY Field1, Field2, Field3, Field4, Field5, Field6 ) AS SN
, *
FROM
myTable
)
,
A -- Get a list of unique combinations of Field1 and [Field2]
AS
(
SELECT Distinct
MIN(SN) OVER(PARTITION BY Field1, Field2) AS SN,
Field1,
Field2
FROM T
)
,
B -- Get a list of all those Field1 values that have more than one [Field2] associated
AS
(
SELECT Field1
FROM A
GROUP BY
Field1
HAVING COUNT(*) > 1
)
SELECT
T.SN,
A.Field1
,A.Field2
,T.Field3
,T.Field4
,T.Field5
,T.Field6
FROM A
INNER JOIN B
ON A.Field1 = B.Field1
INNER JOIN T
ON A.SN = T.SN --If you want rows count from results with 6 fields same as result with 2 fields that shown your script, Use this line and block 2 lines below
--ON A.Field1 = T.Field1 --If you want all rows match field1&2 show with 6 fields, Use these 2 lines and block line up
-- AND A.Field2 = T.Field2