Fetch data if exist in distinct of another column I am trying to generate the query to fetch the data on the basis of company which have tax paid record in each year of : 

Select * 
From (
  Select 
       CompanyName
     , TaxPaidOn
     , DATEPART(Year, TaxPaidOn) as 'TaxPaidYear'
     , OtherInfo
  From
  TestData
) As BaseTable
Where CompanyName IN (Distinct TaxPaidYear)


I know the above query is wrong. Will you please rectify it?

Sample data are as below:

Script :

CREATE TABLE TestData(
    CompanyName nvarchar(50) NOT NULL,
    TaxPaidOn date NOT NULL,
    OtherInfo nvarchar(max) NULL
)
GO
INSERT TestData (CompanyName, TaxPaidon, OtherInfo) 
VALUES 
 ('ABC', '2009-01-01', 'Information1')
,('ABD', '2009-01-01', 'Information2')
,('ABE', '2009-01-01', 'Information3')
,('ABF', '2009-01-01', 'Information4')
,('ABC', '2010-01-01', 'Information5')
,('ABE', '2010-01-01', 'Information6')
,('ABF', '2010-01-01', 'Information7')
,('ABC', '2011-01-01', 'Information8')
,('ABD', '2011-01-01', 'Information9')
,('ABE', '2011-01-01', 'Information10')


TestData Table :

Company Name    TaxPaidOn    OtherInformation
==============================================
ABC             2009-01-01   Information1
ABD             2009-01-01   Information2
ABE             2009-01-01   Information3
ABF             2009-01-01   Information4
ABC             2010-01-01   Information5
ABE             2010-01-01   Information6
ABF             2010-01-01   Information7
ABC             2011-01-01   Information8
ABD             2011-01-01   Information9
ABE             2011-01-01   Information10


Expected Result :

Company Name    TaxPaidOn    OtherInformation
==============================================
ABC             2009-01-01   Information1
ABE             2009-01-01   Information3
ABC             2010-01-01   Information5
ABE             2010-01-01   Information6
ABC             2011-01-01   Information8
ABE             2011-01-01   Information10
I could achieve the result you expected with this query:

;WITH CTE (CompanyName, Total)
AS
(
    SELECT companyName, COUNT(1)
    FROM TestData
    GROUP BY CompanyName
)
SELECT TD.CompanyName, TD.TaxPaidOn, TD.OtherInfo
FROM CTE
    INNER JOIN TestData AS TD 
    ON CTE.CompanyName = TD.CompanyName
WHERE Total = (SELECT MAX(Total) FROM CTE)
ORDER BY TD.TaxPaidOn;


It doesn't have a DISTINCT though.