Declare @FacultyName Nvarchar(100)='Soumoyajyoti'
SELECT C.ContentTypeId,C.Id,V.VersionId,V.VersionName,C.CourseName,C.DeptName,V.ContentId,V.VersionNote
FROM [dbo].[ContentDetails] C inner join [dbo].[VersionDetails] V on C.Id=V.ContentId where V.VersionId in (
select Max(V.VersionId)
FROM [dbo].[ContentDetails] C inner join [dbo].[VersionDetails] V on C.Id=V.ContentId where c.FacultyName LIKE ISNULL(@FacultyName, '') + '%'
group by V.ContentId
) order by V.VersionId desc