July 19, 2011 at 4:57 am
Hi,
I have a View V1 with 3 columns as shown in the Spreadsheet. I want data for which the count of Ref1 is more than 1. ie, if Ref1 repeats more than once I need to retrieve data for those Records as shown in the desired output in Spreadsheet. My query does not get me any data
select Ref1,Ref2,Status
from V1
group by Ref1,Ref2,Status
having COUNT(Ref2)>1
order by Ref1
What am I doing wrong?????
Regards,
Nithin
July 19, 2011 at 5:12 am
WITH CTE
select Ref2
from V1
group by Ref2
having COUNT(Ref2)>1
SELECT * FROM V1 WHERE V1.Ref2 IN (SELECT Ref2 FROM CTE)
July 19, 2011 at 6:10 am
Thaaaaaaaaaaanks a loooooooooooot!!!!!!! for the timely help :-):-):-):-):-):-)!!!!!!
July 19, 2011 at 6:26 am
HTH
and 1 more thing. I think you might better of with a join rather than in. If you have a null in there for ref2 then the results will be wrong.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply