CODEWITHSUNDEEP
sql
Admin Softisans
Admin Softisans

Reputation: 59

Join one table with only 1 first matching record

--------------------Table1-------------
Table1Id            item      SaleId       Dimensions
1                   test1      41           10*11*12
2                   test2      41           13*14*15

--------------------Table2-------------
Table2Id     item         Description   
1           test1         Test element1 desc 1
2           test1         Test element1 desc 2
3           test2         Test element2 desc 1
4           Test2         Test element2 desc 2

Currently I am using following query.

select Table1.Table1Id, Table1.item, Table1.Dimensions, Table2.Description
on Table1.item = Table2.item
where Table1.SaleId = 41

But I want result like that (Need to join Table1.item with first record against item in Table2)

Result :
Table1Id            item            Dimensions       Description
1                   test1           10*11*12          Test element1 desc 1
2                   test2           13*14*15          Test element2 desc 1

Upvotes: 1

Views: 39

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270773

The most typical way for handling this is to use the ANSI-standard row_number():

Select t1.Table1Id, t1.item, t1.Dimensions, t2.Description
from table1 t1 join
     (select t2.*, row_number() over (partition by t2.item order by t2.table2id desc) as seqnum
      from table2 t2
     ) t
     on t1.item = t2.item and t2.seqnum = 1
where t1.SaleId = 41;

Upvotes: 2

Related Questions