table join with priority attribute

Question

Basicly, I have a table with a priority attribute and a value, like

TargetID Priority_Column (int)   Value_column
1                  1             "value 1"
1                  2             "value 2"
1                  5             "value 5
2                  1             "value 1"
2                  2             "value 2"

I want to join another table with this table,

 ID  Name
 1   "name 1"
 2   "name 2"

but only using the row with highest priority.

The result would be like

TargetID Priority_Column (int)  Name        Value_column
1                  5            "name 1"    "value 5"
2                  2            "name 2"    "value 2"

I can of course use a high-level language like python to compute highest priority row for each ID.

But that looks inefficient, is there a way to directly do this in sql?

can u put a example resultset ? Right now it's not very clear what result You want to achieve — Marty, Jan 28 '16 at 22:18

score 3 · Answer 1 · answered Jan 28 '16 at 22:19

3

There are several options for this. Here's one using row_number:

select *
from anothertable a join (
    select *, row_number() over (partition by targetid order by priority desc) rn
    from yourtable) t on a.id = t.targetid and t.rn = 1

answered Jan 28 '16 at 22:19

sgeddes

62,311
6
61
83

Gordon Linoff · Accepted Answer · 2016-01-28T22:39:04.080

2

One way is using outer apply:

select t2.*, t1.*
from table2 t2 outer apply
     (select top 1 t1.*
      from table1 t1
      where t2.id = t1.targetid
      order by priority desc
     ) t1;

I should note that in SQL Server, this is often the most efficient method. It will take good advantage of an index on table1(targetid, priority).

edited Jan 28 '16 at 22:39

answered Jan 28 '16 at 22:20

Gordon Linoff

1,242,037
58
646
786

score 1 · Answer 3 · edited May 23 '17 at 11:59

1

If I understand Your question correctly - You want a "Max By" from the second table..

here's a sample of that https://stackoverflow.com/a/18694271/444149

edited May 23 '17 at 11:59

Community

1
1

answered Jan 28 '16 at 22:20

Marty

3,485
8
38
69

DimPan · Answer 4 · 2016-01-28T22:33:45.200

If I understand correctly what you are asking, you could write an sql query where you would get the max() priority or min() priority accordingly.

In your case, with max priority as a required result the query should be:

select a.TargetID, a.[Value_column], b.ID, b.Name
from TableA a
     join TableB b
        on a.TargetID = b.ID
where a.Priority_Column = (select max(z.Priority_Column) from TableA z 
                          and z.ID = a.TargetID)

... edited to reflect your resultset

score 1 · Answer 5 · answered Jan 28 '16 at 22:44

SELECT  TargetID,
        Priority_Column,
        Name,
        Value_Column
FROM    (SELECT t1.TargetID,
                t1.Priority_Column,
                t2.Name,
                t1.Value_column,
                ROW_NUMBER() OVER (PARTITION BY TargetID ORDER BY Priority_Column DESC) Rn
         FROM   Table1 t1
                JOIN Table2 t2 ON t1.TargetId = t2.ID
        ) t
WHERE   Rn = 1

table join with priority attribute

5 Answers5