Get one record per ID

I’m trying to retrieve data from 2 tables A&B.

(select * from tableA a LEFT JOIN tableB b on a.idA = b.idA)

, there are multiple data rows in B for each PrimaryKey from A. But I want to get only the first record for every ID from tableA. How can I achieve this?

Answer

SQL tables represent unordered sets so there is no first row. But you can get an arbitrary row or specific row based on an ordering column using window functions:

select *
from tableA a LEFT JOIN
     (select b.*,
             row_number() over (partition by idA order by <ordering col>) as seqnum
      from tableB b
     ) b
     on a.idA = b.idA and seqnum = 1