Select multiple values in LIKE Operator

  • I have a SQL query given below, I want to select multiple value using like operator.

    Is my Query correct?

    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%' , 'emp3%' 
    ORDER BY   rx_dt desc
    

    If not, can anyone correct me?

    My table has large amount of data starting with 'emp1' and 'emp3'. Can I filter the result by top 3 "emp1" and top 2 "emp3" based on rx_dt?

  • Andriy M

    Andriy M Correct answer

    3 years ago

    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;
    
  • You are using TOP(1) and order by clause. So you will get only the first top record ordered by the clause.

License under CC-BY-SA with attribution


Content dated before 6/26/2020 9:53 AM