To select like with multiple values in sql -
I guess you want 1 row where employee_id like 'emp1%' and another where employee_id like 'emp3%'. One way to achieve this is to use a union:
SELECT t1.*
FROM
( SELECT top 1 employee_id, employee_ident, utc_dt, rx_dt
FROM employee
JOIN employee_mdata_history
ON employee.ident=employee_mdata_history.employee_ident
WHERE employee_id like 'emp1%'
ORDER BY rx_dt desc
) AS t1
UNION ALL
SELECT t2.*
FROM
( SELECT top 1 employee_id, employee_ident, utc_dt, rx_dt
FROM employee
JOIN employee_mdata_history
ON employee.ident=employee_mdata_history.employee_ident
WHERE employee_id like 'emp3%'
ORDER BY rx_dt desc
) AS t2 ;
Since the legs in the union are guaranteed to be disjoint, a UNION ALL can be used, and that might be a performance advantage compared to using just a UNION.
I believe SQL-server 2008 supports window functions like row_number(), so you can use something like:
SELECT employee_id, employee_ident, utc_dt, rx_dt
FROM (
SELECT employee_id, employee_ident, utc_dt, rx_dt
, row_number() over (partition by substring(employee_id,1,4)
order by rx_dt desc) as rn
FROM employee
JOIN employee_mdata_history
ON employee.ident = employee_mdata_history.employee_ident
WHERE employee_id like 'emp1%'
OR employee_id like 'emp3%'
) as T
WHERE rn = 1
ORDER BY rx_dt desc;
