CODEWITHSUNDEEP
sqljoinmax
Bolinho
Bolinho

Reputation: 11

Cant use join selecting the max element

I am having a trouble in a inner join I want to do this

T1

id   (other atributes)
1
2
3


T2
id      t1_id  order    packat sent    package recievied  other attributes
1       1       0          date*            some date
2       1       1        some date          some date
3       1       2        some date          some date
4       1       3        some date            date**
5       2       0         date*             date**
6       2       0         date*             some date*
7       3       1        some date            date**

Result

Result

T1               T2             T2    package sent  package recievied
atributes   order(0)        order(3)    date*         date**
atributes   order(0)        order(0)    date*         date**
atributes   order(0)        order(1)    date*         date**

I tried different aproaches but I couldnt get this result Something like this

select * from  table1 t1
INNER JOIN table2 t2part1 on(t2part1.t1_id = t1.id and t2part1.ordem =0)
INNER JOIN table t2part2 on (t2part2.t1_id = t1.id and max(t2part2.ordem))

I am using the T1 in another joins

Upvotes: 0

Views: 71

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269853

You can do this with a nested row_number():

select *
from  table1 t1 INNER JOIN
      table2 t2part1
      on t2part1.t1_id = t1.id and t2part1.ordem =0 INNER JOIN
      (select t2part2.*,
              ROW_NUMBER() over (partition by t2part2.id order by t2part2.ordem desc) as seqnum
       from table t2part2
      ) t2part2
      on t2part2.t1_id = t1.id and t2part2.seqnum = 1

Upvotes: 1

Related Questions