SQL Server Select Get Single Row From Another Table -

needing sql server select query here.

i have following tables defined:

usersource

usersourceid id name dept sourceid 1            1  john aaaa 1 2            1  john aaaa 2 3            2  nena bbbb 1 4            2  nena bbbb 2 5            3  gord aaaa 2 6            3  gord aaaa 1 7            4  stan cccc 3 

source

sourceid description rankorder 1        fromhr      1 2        fromtemp    2 3        others      3 

need join both tables , select row rank smallest. such resulting row be:

usersourceid id name dept sourceid description rankorder 1            1  john aaaa 1        fromhr      1 3            2  nena bbbb 1        fromhr      1 6            3  gord aaaa 1        fromhr      1 7            4  stan cccc 3        others      3 

tia.

edit:

here's have come far, seem missing something:

with tablea as( select 1 usersourceid, 1 id, 'john' [name], 'aaaa' [dept], 1 sourceid union select 2, 1, 'john', 'aaaa', 2 union select 3, 2, 'nena', 'bbbb', 1 union select 4, 2, 'nena', 'bbbb', 2 union select 5, 3, 'gord', 'aaaa', 2 union select 6, 3, 'gord', 'aaaa', 1 union select 7, 4, 'stan', 'dddd', 3) , tableb as( select 1 sourceid, 'fromhr' [description], 1 rankorder union select 2, 'fromtemp', 2 union select 3, 'others', 3 )  select distinct tbla.*, tblb.sourceid, tblb.description tableb tblb join tablea tbla on tbla.sourceid = tblb.sourceid left join tableb b2 on b2.sourceid = tblb.sourceid , b2.rankorder < tblb.rankorder b2.sourceid null 

update: scanned tables , there might variations of data. have updated data question above.

practically, need join these 2 tables, , able select row have least rankorder. in case of record usersourceid = 7, particular record selected because there's 1 row exists after tables have been joined.

i use windowed aggregates type of solution pretty regularly. row_number order , number rows based on partition , order specify in over clause.

select  usersoruceid         , id         , name         , dept         , sourceid         , description         , rankorder    (select  usersoruceid                 , id                 , name                 , dept                 , u.sourceid                 , description                 , rankorder                 , row_number() over(partition id order rankorder) ranknum            usersource u          inner join                 source s                on s.sourceid = u.sourceid )   ranknum = 1 

so in case, every id, number rows based on rankorder, , filter view first row.

here's helpful link function microsoft. row_number

----update----

here's rank , row number options.

select  usersoruceid         , id         , name         , dept         , sourceid         , description         , rankorder    (select  usersoruceid                 , id                 , name                 , dept                 , u.sourceid                 , description                 , rankorder                 , row_number() over(partition id order rankorder) row_num                 , rank() over(partition id order rankorder) rank_num --use if want see duplicate records            usersource u          inner join                 source s                on s.sourceid = u.sourceid )   row_num = 1 --rank_num = 1 

replace row_num rank_num view items duplicate rankorder entries