I am trying to write a JPA query because the list of columns (what you see in inner query – like name, color, etc) are dynamic.

This is my query –

select name, color, level, id, type, accountId, viewId from table1 where (name, color, level) in ( select name, color, max(level) from table1 where accountId in account_id_list and viewId in view_id_list group by name, color ) and accountId in account_id_list and viewId in view_id_list

I tried writing a subquery but it does not have multiselect.

I tried writing it as 2 separate multiselect queries where second query depends on the results of first query. But I could not figure out how to write in query with select columns.

Could someone help write this query?