Select Top 1 From a Table For Each row in another Table

I am just starting to work with openedge and I need to join information from two tables but I just need the first row from the second one.

Basically I need to do a tipical SQL Cross Apply but in progress. I look in the documentation and the Statement "FETCH FIRST 10 ROWS ONLY" only in OpenEdge 11.

My query is:

SELECT * FROM la_of PUB.la_ofart ON la_of.empr_cod = la_ofart.empr_cod AND la_of.Cod_Ordf = la_ofart.Cod_Ordf AND la_of.Num_ordex = la_ofart.Num_ordex AND la_of.Num_partida = la_ofart.Num_partida CROSS APPLY (SELECT TOP 1 ofart.Cod_Ordf AS Cod_Ordf_ofart , ofart.Num_ordex AS Num_ordex_ofart FROM la_ofart AS ofart WHERE ofart.empr_cod = la_ofart.empr_cod AND ofart.Num_partida = la_ofart.Num_partida AND la_ofart.doc1_num = ofart.doc1_num AND la_ofart.doc2_linha = ofart.doc2_linha ORDER BY ofart.Cod_Ordf DESC) ofart

Thanks for all help.

Continue reading...