DQL to show repeating attribute at index i at the same time compare another value at index j

Question

Hello ,

I have records with repeating attributes . Data Setup below

Book IdBooknameAuthorsB1Name of Book 2A1

A2

A3B2Name of Book 1A4

A5

I want to write a dql which will be able to display attrubute at first index at the same time compare the second index

like this

select Authors from books where any Authors = 'A3' . I want the result to be A1 .Currently it is A3

Is it possible to select  a value at index that is different from the index being compared.

like select Authors[first index] from books where any Authors ='A3'

PanfilovAB · Accepted Answer

select Authors from books where r_object_id in (
   select r_object_id from books where any Authors ='A3'
) and any i_position=-1 enable(row_based)