How to write a query for the next output?

Question

I have two tables of names and names as shown below.

Platform: SQL Server 2005/2008.

Names table: Nam ID -------------------------------- ----------- A 1 B 2 C 3 D 4 E 5 F 6 G 7 H 8 

and

 name_ids table ID ----------- 3 6 8 

I would like to create the following output connecting these tables.

 Nam Nam_ID ID -------------------------------- ----------- ----------- A 1 NULL B 2 NULL C 3 3 D 4 3 E 5 3 F 6 6 G 7 6 H 8 8 

The logic matches the identifier nam_id and if nam_id is less than any id then return NULL. If nam_id is greater than or equal to id, then return id. Here is the catch. In the above example, for F, 6, we should not return a combination of F, 6,3, but we should only return a match of F, 6,6. when the matching element is found as 6.6, it should skip other matches, such as 6.3. After that, use 7.6, not 7.3. How to write a sql query for the above? The request is time consuming and requires fast execution.

 Scripts: Create table Names(Nam nvarchar(32), ID int); insert into names values('A', 1); insert into names values('B', 2); insert into names values('C', 3); insert into names values('D', 4); insert into names values('E', 5); insert into names values('F', 6); insert into names values('G', 7); insert into names values('H', 8); Create table name_ids( ID int); insert into name_ids values(3); insert into name_ids values(6); insert into name_ids values(8); 

Please, help.

Update: Hey guys, really appreciate your efforts to provide solutions. Now I'm confused to choose the most effective query. I took a few and tried to analyze performance with very large result sets.

Answer 1

 select n.Nam, CASE WHEN ni.ID IS NULL THEN (SELECT MAX(ID) from name_ids n1 where n1.ID < n.ID) ELSE ni.ID END from Names n left join name_ids ni on n.ID = ni.ID 

SqlFiddle

Answer 2

Please check my attempt:

 SELECT DISTINCT Nam, a.ID Nam_ID, MAX(b.ID) OVER (PARTITION BY Nam) ID FROM Names a LEFT JOIN name_ids b ON a.ID>=b.ID 

Answer 3

 select a.Nam, a.ID Nam_ID, isnull(b.ID,(select max(id) from name_ids where id<a.id)) from Names a left outer join name_ids b on (a.ID = b.ID); 

See here DEMO

Answer 4

 select Nam,id, (select TOP 1 id from name_ids where name_ids.id<= Names.id order by name_ids.id desc) from Names order by id 

SQLFiddle demo

Answer 5

Try:

 with cte as (select i.*, row_number() over (order by id) rn from name_ids i) select na.Nam, na.id Nam_id, s.id from Names na left join (select c.id, n.id next_id from cte c left join cte n on c.rn+1 = n.rn) s on na.id >= s.id and na.id < coalesce(s.next_id, na.id+1) 

SQLFiddle here .

Answer 6

 select names.Nam, names.ID, max(name_ids.ID) from names left join name_ids on names.ID >= name_ids.id group by names.Nam, names.ID 

Answer 7

it is simple to do:

 SELECT A.Nam , A.Nam_ID , B.ID FROM NAMES AS A OUTER APPLY ( SELECT MAX(ID) AS ID FROM NAME_IDS AS B WHERE A.ID >= B.ID ) AS B 

I think this is a simpler version of the inline query.