CODEWITHSUNDEEP
sqloracle-database
mcha
mcha

Reputation: 2998

SQL - display a row if the parameter exists , else display another parameter if exists

I have a table like the following : 

ID  |INFO     | DATE_DT
-------------------------
1091|info5    |10/10/2010
1239|old.info |14/09/2010
1340|old.info |07/10/2010
3481|info     |16/10/2010
4134|info3    |21/01/2011

i would like to display just one row with the following conditions :
- if i have in my table one row with INFO = 'info' --> display just this row
- if i dont have one row with INFO = 'info' so i --> display the row with INFO = 'old.info' and DATE_DT = MAX(DATE_DT)

so, in my example if my table is :

ID  |INFO     | DATE_DT
-------------------------
1091|info5    |10/10/2010
1239|old.info |14/09/2010
1340|old.info |07/10/2010
3481|info     |16/10/2010 ===> display this row
4134|info3    |21/01/2011

or if my table doesnt containt INFO = 'info'

ID  |INFO     | DATE_DT
-------------------------
1091|info5    |10/10/2010
1239|old.info |14/09/2010
1340|old.info |07/10/2010 ===> display this row
4134|info3    |21/01/2011

any suggestions?

Thanks.

Upvotes: 1

Views: 305

Answers (2)

Alex Poole
Alex Poole

Reputation: 191580

You can also do this with analytic functions, to only require one pass over the data:

with my_tab as (
select 1091 as id, 'info5' as info, to_date('10/10/2010',' DD/MM/YYYY') as date_dt from dual
union all select 1239, 'old.info', to_date('14/09/2010', 'DD/MM/YYYY') from dual
union all select 1340, 'old.info', to_date('07/10/2010', 'DD/MM/YYYY') from dual
union all select 3481, 'info', to_date('16/10/2010', 'DD/MM/YYYY') from dual
union all select 4134, 'info3', to_date('21/01/2011', 'DD/MM/YYYY') from dual
)
select id, info, to_char(date_dt, 'DD/MM/YYYY')
from (
    select id, info, date_dt, rank() over (order by ord, date_dt desc) as rnk
    from (
        select id, info, date_dt,
            case info
                when 'info' then 1
                when 'old.info' then 2
                when 'info3' then 3
                else null
            end as ord
        from my_tab
    )
)
where rnk = 1;

        ID INFO     DATE_DT
---------- -------- ----------
      3481 info     16/10/2010

Knocking out the 'info' row gives:

        ID INFO     DATE_DT
---------- -------- ----------
      1340 old.info 07/10/2010

Probably no better than @Vincent's for this trivial case, but with more data and more values to choose between this might scale better - just need more ord values in the case, though with any real data I'd have thought you'd look up the precedence from another table...

Upvotes: 2

Vincent Malgrat
Vincent Malgrat

Reputation: 67802

You could select both row and take by priority the one that satisfies the first condition:

SQL> WITH my_table AS (
  2  SELECT 1091 id, 'info5' info, to_date('10/10/2010') date_dt FROM DUAL
  3  UNION ALL SELECT 1239, 'old.info' , to_date('14/09/2010') FROM DUAL
  4  UNION ALL SELECT 1340, 'old.info' , to_date('07/10/2010') FROM DUAL
  5  UNION ALL SELECT 3481, 'info'     , to_date('16/10/2010') FROM DUAL
  6  UNION ALL SELECT 4134, 'info3'    , to_date('21/01/2011') FROM DUAL)
  7  SELECT * FROM (
  8     SELECT 1 ord, t.*
  9       FROM my_table t
 10      WHERE info = 'info'
 11     UNION ALL
 12     SELECT 2 ord, t.*
 13       FROM my_table t
 14      WHERE date_dt = (SELECT MAX(date_dt) FROM my_table)
 15     ORDER BY ord)
 16   WHERE ROWNUM = 1;

       ORD         ID INFO     DATE_DT
---------- ---------- -------- -----------
         1       3481 info     16/10/2010

If you remove the row 'info', the row where DATE_DT = MAX(DATE_DT) will be chosen:

SQL> WITH my_table AS (
  2  SELECT 1091 id, 'info5' info, to_date('10/10/2010') date_dt FROM DUAL
  3  UNION ALL SELECT 1239, 'old.info' , to_date('14/09/2010') FROM DUAL
  4  UNION ALL SELECT 1340, 'old.info' , to_date('07/10/2010') FROM DUAL
  5  /*UNION ALL SELECT 3481, 'info'     , to_date('16/10/2010') FROM DUAL*/
  6  UNION ALL SELECT 4134, 'info3'    , to_date('21/01/2011') FROM DUAL)
  7  SELECT * FROM (
  8     SELECT 1 ord, t.*
  9       FROM my_table t
 10      WHERE info = 'info'
 11     UNION ALL
 12     SELECT 2 ord, t.*
 13       FROM my_table t
 14      WHERE date_dt = (SELECT MAX(date_dt) FROM my_table)
 15     ORDER BY ord)
 16   WHERE ROWNUM = 1;

       ORD         ID INFO     DATE_DT
---------- ---------- -------- -----------
         2       4134 info3    21/01/2011

Upvotes: 3

Related Questions