December 4, 2009 at 1:05 am
Hi i have following table (very big table)
columns
ID , CODE ,...............................
values
1 , X , ..........
1, X ,...........
2, A,............
2, A,............
3, A,............
3, A,............
4, C,............
4, D,............
.
.
.
i need to find out all rows which are having more than one value for each ID
example 4 is having C and D
can any one help
Thanks
Salil
December 4, 2009 at 1:19 am
untested, but it should work:
;WITH cte AS
(
SELECT ID, CODE FROM MyTable GROUP BY ID, CODE
)
SELECT ID, COUNT(*) AS CNT
FROM CTE
GROUP BY ID
HAVING COUNT(*) > 1
December 4, 2009 at 1:39 am
Another way:
;WITH cte AS
(
SELECT ID
,dense_rank() over ( partition by ID order by ID,) AS R
FROM MyTable
)
SELECT ID
FROM cte
WHERE R > 1
GROUP BY ID
-Vikas Bindra
December 4, 2009 at 2:03 am
SELECT ID
FROM MyTable
GROUP BY ID
HAVING COUNT(DISTINCT CODE)>1
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537December 4, 2009 at 2:33 am
SELECT *
FROM very big table tblo
WHERE EXISTS
(
SELECT 1
FROM very big table tbln
WHERE tbln.id <> tblo.id
AND tbln.code = tblo.code
)
OR EXISTS
(
SELECT 1
FROM very big table tbla
WHERE tbla.id = tblo.id
AND tbla.code <> tblo.code
)
December 4, 2009 at 3:06 am
thanks for help query is working fine
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply