Search code examples
db2db2-luw

DB2 Selective Select

Is it possible select only the columns from the base table without list all the columns, meaning all columns wihout rn column.

This will not work...

select tt.t.*
from (

Origin:

select *
from (
    select t.*, row_number() over(partition by id order by anumber) rn 
    from mytable t 
    where option
) tt
where rn = 1
order by id
Solution

  • Assuming this related to this question :

    You can do it by joining the subquery with your table :

    select t.*
from (
    select t.*, row_number() over(partition by id order by anumber) rn    
    from mytable t 
    where option
) tt
inner join mytable t on t.ID = tt.ID and t.ANUMBER = tt.ANUMBER
where rn = 1
order by t.id