--
SELECT TOP(20) * FROM (SELECT row_number() OVER(ORDER by 10*dbo.MatchJobCategory(U.JobCategory,4) + 10*dbo.MatchDate(U.LastVisitedOn,180) + 10*(CASE WHEN 'Bangalore' <> '' AND ('Bangalore' = U.City OR U.JobCity = 'Bangalore') THEN 1 ELSE babajob.dbo.MatchNearby(babajob.dbo.LatLongDistance(12.98561775,77.59297157,U.lat,U.long),100) END) + 0 + 0 + 0 + 0 + 0 + 10*dbo.MatchEducation(U.EducationLevel,0) DESC) as rownum, babajob.dbo.LatLongDistance(12.98561775,77.59297157,U.lat,U.long) as Distance, U.*, U.FirstName + ' ' + U.LastName 'UserName', L.Mobile# 'LoginMobile', L.EmailID 'LoginEmailId', c.JobCategoryDescription, t.TimingsDescription from [UserInfo] as U with (nolock)INNER JOIN JobCategory as c with (nolock) ON U.JobCategory = c.JobCategoryId INNER JOIN Timings as t with (nolock) ON t.TimingsId = U.Timings INNER JOIN Login as L with (nolock) ON L.UserID = U.UserID WHERE U.ProfileVisibility > 11 and U.needjob='true' and U.isdeleted='false' and (U.EmailID <> '' or U.Mobile# <> '') AND U.Country = 'India' AND U.LastVisitedOn > '2011-11-28 00:00:00.000' AND U.JobCategory IN (4) AND (dbo.TRIM(U.FirstName) <> 'mobile' AND dbo.TRIM(U.FirstName) <> 'mobile user') AND U.DefaultPurpose <> 4 AND ((U.EmailId = '' OR U.EmailId = L.EmailId) OR U.Mobile# <> '') AND (U.city ='Bangalore' OR U.Jobcity ='Bangalore') ) as smstemp WHERE rownum>0
