sql - select single row based on count of rows present oracle -


i've got below table using query, want fetch single record based on conditions explained below , assign 2 variable i.e. v_dte_meeting , v_status_meeting declared in stored procedure,

 dte_meeting| ststus_meeting ######################## 15-oct-14   | due     30-oct-14   | due 15-dec-14   | init 30-dec-14   | init 30-nov-15   | approved

i want assign value these variables based on below conditions:

  1. if a single or multiple records present status_meeting 'due' assign v_dte_meeting greatest date 'due' status , assign v_status_meeting value 'due'
  2. if above condition fails then, check if single or multiple records present ststus_meeting 'init' if does, assign v_dte_meeting greatest date 'init' status , assign v_status_meeting value 'init'
  3. if both condition fails assign both variables null value

please me best way in oracle

hope helps. showing select statement (i didn't create variables, not selecting into, wasn't difficulty, know how that.) use subquery factoring (the clause), available in versions >= 11 believe, otherwise can rewrite put subqueries belong.

note use of rank(); in gordon's solution, pick max(dte) on rows, not status = 'due', can't simple wrote. edit: don't see selects null, null if neither 'due' nor 'init' present. (sorry abuse, should comment answer, lack privileges.)

with t (date_meeting, status_meeting)    (       select to_date('15-oct-14', 'dd-mon-yy'), 'due' dual union       select to_date('30-oct-14', 'dd-mon-yy'), 'due' dual union       select to_date('15-dec-14', 'dd-mon-yy'), 'init' dual union       select to_date('30-dec-14', 'dd-mon-yy'), 'init' dual union       select to_date('15-nov-15', 'dd-mon-yy'), 'approved' dual    ),      s (date_meeting, status_meeting)    (       select date_meeting, status_meeting t              status_meeting = 'due' or status_meeting = 'init'       union select null, null dual  -- ensure have nulls if needed    ),     r (date_meeting, status_meeting, rk)    (       select date_meeting, status_meeting,               rank() on (order decode(status_meeting, 'due', 0, 'init', 1, 2),                            date_meeting desc)  -- make sure understand       s    ) select date_meeting, status_meeting r rk = 1 /

result:

date_meet status_m --------- -------- 30-oct-14 due  1 row selected.

Comments

Post a Comment