![]() ON employee.ident = employee_mdata_history. , row_number() over (partition by substring(employee_id,1,4) SELECT employee_id, employee_ident, utc_dt, rx_dt 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 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. ( SELECT top 1 employee_id, employee_ident, utc_dt, rx_dt One way to achieve this is to use a union: SELECT t1.* I guess you want 1 row where employee_id like 'emp1%' and another where employee_id like 'emp3%'. In addition I'll add a pattern search, but this solution returns all records that matches the pattern: LIKE 'emp%' 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. Keep in mind that you're using TOP 1, you'll get one row maximum, no matter how many conditions you use. I've set up an example: create table employees(employee_id varchar(10), employee_name varchar(100)) Have a look at OR (Transact-SQL) on MS-Docs. Their syntax is identical, but LIKE is case-sensitive, while ILIKE is case-insensitive. ON employee.ident = employee_mdata_history.employee_ident LIKE and ILIKE allow pattern matching within character-based column data. You should use an OR/AND condition: SELECT TOP (1) INNER JOIN dbo.employee_mdata_history AS h LIKE is case sensitive, ILIKE is case insensitive. You can always use short aliases to avoid repeating potentially long table names. SQL Commands: LIKE and ILIKE Published on Maby Kevin Graham The predicates LIKE and ILIKE are used to search for strings that match a given pattern, so you can search or for a single word (or string) in a long text field. That makes your query easier to read/understand. to every pattern, limiting the number of rows for each pattern to the corresponding value from the pattern list.Īs 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. The CROSS APPLY operator applies your query to every row of the pattern list, i.e. 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. ON employee.ident=employee_mdata_history.employee_ident ![]() ![]() Alternatively you can try the following method: SELECTĮmployee_id, employee_ident, utc_dt, rx_dt
0 Comments
Leave a Reply. |