oracle - PL/SQL perform multiple selects looping over array of values, aggregating result -

i need produce set of rows wherein each 1 result of complex select, , each select based on value list.

in other words, need perform task like:

select * (      select col1, col2, col3 data_table guid=my_guid      order col1)     rownum<=1 

except need perform sequence of values of my_guid taken table, , union of results suitable selecting from.

simply saying where guid in (select * guid_list) produce 1 result because of rownum, want top-ranked row associated each guid instead.

i'm having trouble figuring out correct way is.

in sql variant aggregator returns first result, do:

select col1, first(col2), first(col3) data_table     order col1 group guid 

while debugging, don't have write access database, i'd avoid solutions involving creating temporary tables, procedures, or functions.

you can join tables (rather using second subquery, though work), , use analytic function assign ranking results based on each guid. like:

select col1, col2, col3 (    select dt.col1, dt.col2, dt.col3,     row_number() on (partition dt.guid order col1) rn   guid_list gl   join data_table dt on dt.guid=gl.guid  ) rn = 1; 

you can include guid in subquery if need in result set.

if want aggregate functions , without subquery like:

select min(dt.col1) col1,   min(col2) keep (dense_rank first order dt.col1) col2,   min(col3) keep (dense_rank first order dt.col1) col3 guid_list gl join data_table dt on dt.guid=gl.guid  group dt.guid; 

read more keep dense_rank first..