SELECT DISTINCT TOP 10000 d_vacancies.k_id , dbo.fn_getLangItem(d_vacancies.l_durationID, 1,4) as tx_duration, d_vacancies.tx_benefits, d_vacancies.l_contactID, d_vacancies.tx_altText, d_vacancies.d_date, d_vacancies.yn_negotiable, d_vacancies.tx_ref, d_vacancies.tx_jobTitle, d_vacancies.l_clientID, d_vacancies.l_currencyID, d_vacancies.f_salMin,d_vacancies.f_salMax,d_vacancies.b_salaryType,d_vacancies.l_ote,d_vacancies.l_durationID, d_vacancies.tx_city, (select count(k_id) from d_vacancies where yn_activated=1 and yn_filled=0) as vacTotalCount, d_vacancies.yn_passportReq, d_vacancies.yn_featured FROM d_vacancies LEFT OUTER JOIN x_vacItems ON d_vacancies.k_id = x_vacItems.l_vacID LEFT OUTER JOIN x_vacLocations ON d_vacancies.k_id = x_vacLocations.l_vacID LEFT OUTER JOIN l_locations ON x_vacLocations.l_locationID = l_locations.k_id LEFT OUTER JOIN x_vacSectors ON d_vacancies.k_id = x_vacSectors.l_vacID WHERE d_vacancies.yn_activated =1 AND NOT yn_filled =1 AND ( ) ORDER BY d_vacancies.yn_featured DESC, d_vacancies.k_id DESC
