Q:

MySQL/hive query for one result per item in an array

I have a list of ids I am interested in selecting data for in hive. For each of these ids, there are one or more entities stored in a table called books that have this id in a global_parent_id column. I’m wondering if there’s a query I can write where I pass in the list of ids and for each id in the list, get only ONE entry from books that has the id as its global_parent_id. I was trying this Hive query (will accept MySQL suggestions too), but it only returns one result, whereas I want to return one child book id for each parent book id. Thanks in advance.
select distinct(book_id) from books where global_parent_id in (‘23434’, 23425’, ‘22322’) limit 1;

A:

limit 1 will only return 1 record even if multiple rows satisfy the condition. Use row_number function in Hive.(Change order by in row_number depending on your rules)
select global_parent_id,child_book_id
from (select global_parent_id,child_book_id,
      row_number() over(partition by global_parent_id order by child_book_id) as rnum
      from books 
      where global_parent_id in ('23434', '23425', '22322')
     ) t
where rnum=1

