misio_jasio misio_jasio - 1 year ago 83
SQL Question

How to get min value from set

I have this table:

Profile_id Phase_id order
30087853 30021628 525
30087853 30021635 523
30087853 30021673 122
30087853 30021703 521

from the above I would like to get profile_id along with this phase_id, which has lowest order_num, so the outcome will be like:

Profile_id Phase_id order
30087853 30021673 122

Answer Source

You didn't specify your DBMS so this is standard SQL:

select profile_id, phase_id, "order"
from (
   select profile_id, phase_id, "order", 
          row_number() over (partition by profile_id order by "order") as rn
   from the_table
) t
where rn = 1;

Online example: http://rextester.com/MAUV44954

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download