Archives of the TeradataForum

Message Posted: Thu, 09 Sep 2004 @ 06:33:51 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Dirk Lerner wrote:

There's no way to select only one row, but you can get rid of the extra row:

     sel * from bla1 a
     left join bla2 b
     on a.bdf=b.bdf
     and a.sai=b.sai
     and a.ek_akt=b.ek_akt
     and (a.genre=b.genre or b.genre=0)
     qualify
        rank() over (partition by a.bdf,a.sai,a.ek_akt, a.genre
                     order by b.genre desc) = 1

But rank is a huge overhead, maybe it's better to do an extra join:

     sel a.*,b2.*
     from bla1 a
     left join bla2 b
     on a.bdf=b.bdf
     and a.sai=b.sai
     and a.ek_akt=b.ek_akt
     and a.genre=b.genre
     left join bla2 b2
     on a.bdf=b2.bdf
     and a.sai=b2.sai
     and a.ek_akt=b2.ek_akt
     and coalesce(b.genre,0)=b2.genre

Dieter

	<Prev	Next>		<<First	<Prev	Next>	Last>>