Select multiple values in LIKE Operator
Alternatively you can try the following method:
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
;
The VALUES
row constructor represents your pattern list as a table, additionally supplying each pattern with the number of rows to retrieve for that pattern. The CROSS APPLY operator applies your query to every row of the pattern list, i.e. to every pattern, limiting the number of rows for each pattern to the corresponding value from the pattern list.
As a side note, please let me take this opportunity to suggest that you always qualify your columns with the table alias in a query that is reading from two or more tables. That makes your query easier to read/understand. You can always use short aliases to avoid repeating potentially long table names. For instance:
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
...
You should use an OR/AND condition:
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;
Have a look at OR (Transact-SQL) on MS-Docs.
I've set up an example:
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
employee_id | employee_name :---------- | :-------------- emp10 | Bryan Nelson emp12 | Rosalyn Sanders emp13 | Rose Tudler emp30 | Ian McGregor
Keep in mind that you're using TOP 1, you'll get one row maximum, no matter how many conditions you use.
SELECT TOP 1 employee_id, employee_name FROM employees WHERE employee_id LIKE 'emp1%' OR employee_id LIKE 'emp3%'; GO
employee_id | employee_name :---------- | :------------ emp10 | Bryan Nelson
If you need the TOP (X) rows WHERE employee_id LIKE 'emp1%'
plus TOP (X) rows WHERE employee_id LIKE 'emp3%'
you can use two select statements joined with 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
employee_id | employee_name :---------- | :-------------- emp10 | Bryan Nelson emp12 | Rosalyn Sanders emp13 | Rose Tudler emp30 | Ian McGregor
In addition I'll add a pattern search, but this solution returns all records that matches the pattern: LIKE 'emp[13]%'
SELECT employee_id, employee_name FROM employees WHERE employee_id LIKE 'emp[13]%' GO
employee_id | employee_name :---------- | :-------------- emp10 | Bryan Nelson emp12 | Rosalyn Sanders emp13 | Rose Tudler emp30 | Ian McGregor
dbfiddle here
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 is 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;