-1

I have a set of aggregate records for which I need to join with another table that has columns with & without null values and pull a record with highest priority when there is a multiple match. Table 2 Columns can have null values.

Table 1

col1 col2 col3 col4

Table 2

col1 col2 col3 col4 col5 col6 col7 priority
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
manikanth
  • 9
  • 1
  • If you're using MySQL, this may be what you're looking for: [SQL: Find the max record per group [duplicate]](http://stackoverflow.com/questions/2657482/sql-find-the-max-record-per-group) Also, browse the [tag:greatest-n-per-group] – Leif Jones Dec 10 '16 at 18:15
  • Which colum(s) is the join on? Why does it matter that there may be nulls in the second table? Also, when there is more than one possible match and the priority is the same, which rows should be matched? (Or is the priority a strict ordering, where ties are not possible?) –  Dec 10 '16 at 18:27
  • [edit] your question and add some sample data and the expected output based on that data. [_Formatted_](http://dba.stackexchange.com/help/formatting) **text** please, [no screen shots](http://meta.stackoverflow.com/questions/285551/why-may-i-not-upload-images-of-code-on-so-when-asking-a-question/285557#285557) –  Dec 10 '16 at 18:43

1 Answers1

1

Use row_number():

select t1.*, t2.*
from t1 join
     (select t2.*,
             row_number() over (partition by ?? order by priority desc) as seqnum
      from t2
     ) t2
     on t1.?? = t2.??
where seqnum = 1;

The ?? represents the column(s) used for joining the two tables together. For instance, if you wanted the highest priority for matches on col1:

select t1.*, t2.*
from t1 join
     (select t2.*,
             row_number() over (partition by col1 order by priority desc) as seqnum
      from t2
     ) t2
     on t1.col1 = t2.col1
where seqnum = 1;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • I appreciate your answer. I have this in mind. But my question is, the join is not on one column, it has to be on at least with 5-7 columns not knowing which column of table2 has a null value. – manikanth Dec 10 '16 at 19:29