Selecione vários valores no operador LIKE

Alternativamente, você pode tentar o seguinte método:

SELECT
  x.*
FROM
  (
    VALUES
      ('emp1%', 3),
      ('emp3%', 2)
  ) AS v (pattern, row_count)
  CROSS APPLY
  (  -- your query
    SELECT top (v.row_count)
               employee_id, employee_ident, utc_dt, rx_dt 
    FROM       employee
    INNER JOIN employee_mdata_history
    ON         employee.ident=employee_mdata_history.employee_ident 
    WHERE      employee_id like v.pattern
    ORDER BY   rx_dt desc
  ) AS x
;

O VALUESconstrutor de linha representa sua lista de padrões como uma tabela, fornecendo adicionalmente a cada padrão o número de linhas a serem recuperadas para esse padrão. O operador CROSS APPLY aplica sua consulta a cada linha da lista de padrões, ou seja, a cada padrão, limitando o número de linhas de cada padrão ao valor correspondente da lista de padrões.

Como observação lateral, deixe-me aproveitar esta oportunidade para sugerir que você sempre qualifique suas colunas com o alias da tabela em uma consulta que está lendo de duas ou mais tabelas. Isso torna sua consulta mais fácil de ler/compreender. Você sempre pode usar aliases curtos para evitar a repetição de nomes de tabela potencialmente longos. Por exemplo:

SELECT TOP (1)
  e.employee_id,
  h.employee_ident,
  ...
FROM
  dbo.employee AS e
  INNER JOIN dbo.employee_mdata_history AS h
    ON e.ident = h.employee_ident
WHERE
  e.employee_id LIKE ...
ORDER BY
  ...

Você deve usar uma condição OR/AND:

SELECT TOP (1) 
           employee_id, employee_ident, utc_dt, rx_dt 
FROM       employee
INNER JOIN employee_mdata_history 
ON         employee.ident = employee_mdata_history.employee_ident 
WHERE      employee_id like 'emp1%' 
OR         employee_id like 'emp3%' 
ORDER BY   rx_dt desc;

Dê uma olhada em OR (Transact-SQL) em MS-Docs.

Montei um exemplo:

create table employees(employee_id varchar(10), employee_name varchar(100));

insert into employees values
('emp10', 'Bryan Nelson'),
('emp12', 'Rosalyn Sanders'),
('emp13', 'Rose Tudler'),
('emp20', 'Julio Gomez'),
('emp30', 'Ian McGregor'),
('emp40', 'Anne Hatt');
GO

SELECT employee_id, employee_name
FROM   employees
WHERE  employee_id LIKE 'emp1%'
OR     employee_id LIKE 'emp3%';
GO

funcionário_id | nome do empregado  
:---------- | :--------------
emp10 | Bryan Nelson   
emp12 | Rosalyn Sanders
emp13 | Rose Tudler    
emp30 | Ian McGregor   

Tenha em mente que você está usando o TOP 1, você obterá no máximo uma linha, não importa quantas condições você use.

SELECT TOP 1 employee_id, employee_name
FROM   employees
WHERE  employee_id LIKE 'emp1%'
OR     employee_id LIKE 'emp3%';
GO

funcionário_id | nome do empregado
:---------- | :------------
emp10 | Bryan Nelson

Se você precisar das linhas TOP (X) WHERE employee_id LIKE 'emp1%'mais as linhas TOP (X) WHERE employee_id LIKE 'emp3%', você pode usar duas instruções select unidas com UNION ALL.

SELECT TOP 3 employee_id, employee_name
FROM   employees
WHERE  employee_id LIKE 'emp1%'
UNION ALL
SELECT TOP 1 employee_id, employee_name
FROM   employees
WHERE  employee_id LIKE 'emp3%'
GO

funcionário_id | nome do empregado  
:---------- | :--------------
emp10 | Bryan Nelson   
emp12 | Rosalyn Sanders
emp13 | Rose Tudler    
emp30 | Ian McGregor   

Além disso, adicionarei uma pesquisa de padrão, mas esta solução retorna todos os registros que correspondem ao padrão: LIKE 'emp[13]%'

SELECT employee_id, employee_name
FROM   employees
WHERE  employee_id LIKE 'emp[13]%'
GO

funcionário_id | nome do empregado  
:---------- | :--------------
emp10 | Bryan Nelson   
emp12 | Rosalyn Sanders
emp13 | Rose Tudler    
emp30 | Ian McGregor   

dbfiddle aqui

Eu acho que você quer 1 linha where employee_id like 'emp1%'e outra where employee_id like 'emp3%'. Uma maneira de conseguir isso é usar um 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 ;

Uma vez que as pernas na união são garantidamente disjuntas UNION ALL, pode ser usado, e isso pode ser uma vantagem de desempenho em comparação com o uso de apenas um UNION.

Acredito que o SQL-server 2008 suporta funções de janela como row_number(), então você pode usar algo como:

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;

este é um bom argumento. Caso você precise que todas as palavras da string estejam presentes em um campo de uma tabela, você pode usar o select que você encontra abaixo. A consulta usa a instrução PATINDEX que, como você sabe, só funciona se as palavras na string estiverem na mesma ordem do campo de pesquisa. De fato, os dois segmentos do PATINDEX são primeiro classificados e depois agregados. A pré-condição é a existência de uma função de tabela (splitstring_ex) que retorna uma lista de registros... daí uma divisão trivial.

select 
PATINDEX(
    (
        select '%'+STRING_AGG(z.myvalue,'%')+'%' 
        from (
                select top 100
                    [name] as myvalue
                from 
                    dbo.splitstring_ex('JKK ABC ZGT', ' ') 
                order by
                    [name]
            ) as z
    )
    ,(
        select STRING_AGG(z.myvalue,' ')
        from (
                select top 100
                    [name] as myvalue
                from 
                    dbo.splitstring_ex('AAK JKK ZGT ABC MKI', ' ') 
                order by
                    [name]
            ) as z
    )
) 

Você está usando TOP(1)e order bycláusula. Portanto, você obterá apenas o primeiro registro superior ordenado pela cláusula.