Home » SQL & PL/SQL » SQL & PL/SQL » Result based on conditional where clause
Result based on conditional where clause [message #436652] Thu, 24 December 2009 21:22 Go to next message
renjivij
Messages: 17
Registered: October 2008
Junior Member
Oracle 10g

create table datatest
(primaryid number,
id number,
feed varchar2(4),
alias varchar2(15))

insert into datatest values(1,100,'CNN','NEWS');
insert into datatest values(2,100,'BBC','SPORTS');
insert into datatest values(3,101,'CNN','INV')
insert into datatest values(4,101,'ABC','MON')
insert into datatest values(5,102,'BBC','REAL')
insert into datatest values (6,103,'NEC','TVL')

Im looking for a query which returns rows with feed CNN
If feed CNN is not there then return the row with feed BBC
This is the result im looking for.

1,100,CNN,NEWS
3,101,CNN,INV
5,102,BBC,REAL

thank you in advance
Re: Result based on conditional where clause [message #436653 is a reply to message #436652] Thu, 24 December 2009 21:25 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
so CNN OR BBC

just a simple SELECT

Re: Result based on conditional where clause [message #436660 is a reply to message #436653] Thu, 24 December 2009 22:53 Go to previous messageGo to next message
renjivij
Messages: 17
Registered: October 2008
Junior Member
CNN OR BBC condition will return
primary  ID      feed   alias
1	100	CNN	NEWS
2	100	BBC	SPORTS
3	101	CNN	INV
5	102	BBC	REAL


Im looking for CNN rows if exists else BBC for each ID.
primary  ID      feed   alias
1	100	CNN	NEWS
3	101	CNN	INV
5	102	BBC	REAL

Re: Result based on conditional where clause [message #436677 is a reply to message #436660] Fri, 25 December 2009 03:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Im looking for CNN rows

So why there is a BBC row in your result?

Quote:
if exists else BBC for each ID.

This is clear as mud.

Explain why the rows you display in your result are part of the result, explain it with words for each row.
In short:
why there is "1,100,CNN,NEWS"?
Why there is "3,101,CNN,INV"?
Why there is "5,102,BBC,REAL"?

Do you mean for each "id" you want the "CNN" if it exists, else you the "BBC" one if it exists else you want no row for this "id"?

Regards
Michel
Re: Result based on conditional where clause [message #436681 is a reply to message #436677] Fri, 25 December 2009 06:04 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
ID 100 contains a row with CNN
ID 101 contains a row with CNN
ID 102 does NOT contain a row with CNN but DOES contain a row with BBC
103 Contains neither CNN nor BBC.

@OP Use a RANK function with a DECODE in the order by :

RANK() OVER (PARTITION BY id ORDER BY DECODE(feed, 'CNN', 1, 'BBC', 2)

You can then filter appropriately.
Re: Result based on conditional where clause [message #436682 is a reply to message #436681] Fri, 25 December 2009 06:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Thanks misread OP's question.

Regards
Michel
Re: Result based on conditional where clause [message #436694 is a reply to message #436681] Fri, 25 December 2009 11:41 Go to previous messageGo to next message
renjivij
Messages: 17
Registered: October 2008
Junior Member
rank() function was what i was lookin for ..
Thanks a lot for the help.
Re: Result based on conditional where clause [message #436827 is a reply to message #436652] Mon, 28 December 2009 12:57 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
someone please provide a finished solution so others (including myself) can formulate a generalized solution for this problem.

Thanks, Kevin
Re: Result based on conditional where clause [message #436830 is a reply to message #436827] Mon, 28 December 2009 13:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Maybe this one:
SQL> select * from datatest;
 PRIMARYID         ID FEED ALIAS
---------- ---------- ---- ---------------
         1        100 CNN  NEWS
         2        100 BBC  SPORTS
         3        101 CNN  INV
         4        101 ABC  MON
         5        102 BBC  REAL
         6        103 NEC  TVL

6 rows selected.

SQL> select *
  2  from ( select t.*, 
  3                rank () over (partition by id order by decode(feed,'CNN',1,'BBC',2,3)) rk
  4         from datatest t
  5       )
  6  where rk = 1
  7  /
 PRIMARYID         ID FEED ALIAS                   RK
---------- ---------- ---- --------------- ----------
         1        100 CNN  NEWS                     1
         3        101 CNN  INV                      1
         5        102 BBC  REAL                     1
         6        103 NEC  TVL                      1

4 rows selected.

Regards
Michel
Re: Result based on conditional where clause [message #436837 is a reply to message #436652] Mon, 28 December 2009 14:28 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
This is great. So now we generalize the problem and note that a generalized approach can be use to answer the non-specific question:

Quote:
For each key... I want the best row out of a set of rows... based on some set of conditions.

The general solution then follows this idea:

Quote:
Translate your conditions to a common space of values (integer numbers is most common) and then use RANK to get the best row.

Thanks everyone, Kevin.
Previous Topic: Need help with Procedure Error Handling
Next Topic: Execute UNIX script from PL/SQL procedure
Goto Forum:
  


Current Time: Sat Dec 03 08:17:05 CST 2016

Total time taken to generate the page: 0.13410 seconds