Home » SQL & PL/SQL » SQL & PL/SQL » SQL Help (Oracle 9.2.0.8)
SQL Help [message #326259] Tue, 10 June 2008 22:10 Go to next message
lowcash75
Messages: 67
Registered: February 2006
Member
Trying to figure out a query to return results based on a particular code order.

I have a table with two columns ID and code.

Example:

ID Code
-- ----
1 A
1 B
1 C
2 B
2 C
2 D
3 C
4 B
4 B

My criteria is to check is to check if a particular ID has a code of A, then return A, otherwise check if C is available, then return C, otherwise check if D is available and then finally check B. I can do it in a function but trying to see if there is a way to run it in a select sql.

So, the results should be

ID Code
-- ----
1 A
2 C
3 C
4 B

DDL:
create table test (id number, code varchar2(1));

insert into test (id, code) values (1, 'A');
insert into test (id, code) values (1, 'B');
insert into test (id, code) values (1, 'C');
insert into test (id, code) values (2, 'B');
insert into test (id, code) values (2, 'C');
insert into test (id, code) values (2, 'D');
insert into test (id, code) values (3, 'C');
insert into test (id, code) values (4, 'B');
insert into test (id, code) values (4, 'B');

commit;

Thanks for your help in advance!!
Re: SQL Help [message #326266 is a reply to message #326259] Tue, 10 June 2008 23:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Have a look at ROW_NUMBER, RANK and DENSE_RANK functions and use DECODE/CASE for the order.

Regards
Michel
Re: SQL Help [message #326272 is a reply to message #326266] Tue, 10 June 2008 23:25 Go to previous messageGo to next message
apps_user
Messages: 35
Registered: May 2008
Member
Your ID doesnot have 'A' or 'B'.
So it should be based on value of code.
If your logic is based on values of code then you have to
try this
select
DECODE(code,'A','A','B','B','C','C','D','D','B')
from test;

[Updated on: Tue, 10 June 2008 23:28]

Report message to a moderator

Re: SQL Help [message #326278 is a reply to message #326272] Tue, 10 June 2008 23:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is not the wanted order.
In addition, it assumes the order of the letters in OP's language is the same than in yours.

Regards
Michel
Re: SQL Help [message #326331 is a reply to message #326259] Wed, 11 June 2008 02:07 Go to previous messageGo to next message
seyed456
Messages: 220
Registered: May 2008
Location: south india .
Senior Member

hey lowcash ...
i cudnt understand u fully ..
so far i was trying to adopt by max and min functions ..

MAX function


SELECT   id,MAX( DECODE (code, 'A','A','C','C','D','D','B') )
                  FROM test123
                  GROUP BY id



        ID M
---------- -
         1 C
         2 D
         3 C
         4 B


4 rows selected.
                  



MIN function
 SELECT   id,Min( DECODE (code, 'A','A','C','C','D','D','B') )
                  FROM test123
                  GROUP BY id
                  order by id




      ID M
---------- -
         1 A
         2 B
         3 C
         4 B


4 rows selected.











Re: SQL Help [message #326344 is a reply to message #326331] Wed, 11 June 2008 02:44 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Wed, 11 June 2008 06:07
Have a look at ROW_NUMBER, RANK and DENSE_RANK functions and use DECODE/CASE for the order.

Regards
Michel


What does "hey lowcash ..." mean?
"i cudnt understand u fully .." -> use standard english


Re: SQL Help [message #326526 is a reply to message #326259] Wed, 11 June 2008 22:11 Go to previous messageGo to next message
lowcash75
Messages: 67
Registered: February 2006
Member
Michel,
Could you give me an example of how any of those functions you listed will work for me.

Thanks!
Re: SQL Help [message #326529 is a reply to message #326331] Wed, 11 June 2008 22:14 Go to previous messageGo to next message
lowcash75
Messages: 67
Registered: February 2006
Member
Seyed,
Yeah the min/max won't work. There is a particular criteria I have to follow return results. For a particular ID I have to see if any records have a CODE of A, if no A, then C, if no A or C, then D, if no A or C or D then B.

Thanks!
Re: SQL Help [message #326530 is a reply to message #326259] Wed, 11 June 2008 22:21 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
Depending upon the kindness of strangers is not a wise move and goes in the opposite direction of real learning.
Re: SQL Help [message #326573 is a reply to message #326526] Thu, 12 June 2008 00:52 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
lowcash75 wrote on Thu, 12 June 2008 05:11
Michel,
Could you give me an example of how any of those functions you listed will work for me.

Thanks!

Click on the links I posted there are examples, try to use them for your case and come back with your tries or solution.

Regards
Michel
Re: SQL Help [message #326650 is a reply to message #326529] Thu, 12 June 2008 02:57 Go to previous messageGo to next message
seyed456
Messages: 220
Registered: May 2008
Location: south india .
Senior Member

lowcash75 wrote on Wed, 11 June 2008 22:14
Seyed,
Yeah the min/max won't work. There is a particular criteria I have to follow return results. For a particular ID I have to see if any records have a CODE of A, if no A, then C, if no A or C, then D, if no A or C or D then B.

Thanks!




yea i will try and get back to u lowacash . ..
Re: SQL Help [message #326706 is a reply to message #326529] Thu, 12 June 2008 06:01 Go to previous message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Quote:

Yeah the min/max won't work. There is a particular criteria I have to follow return results. For a particular ID I have to see if any records have a CODE of A, if no A, then C, if no A or C, then D, if no A or C or D then B.


Are you sure what you said ?

Check this link

http://download.oracle.com/docs/cd/B10501_01/server.920/a96540/functions45a.htm#1000859

http://www.orafaq.com/forum/t/120221/0/

Regards

Raj

[Updated on: Thu, 12 June 2008 06:01]

Report message to a moderator

Previous Topic: like to know more grants
Next Topic: How unique index duplicated ?
Goto Forum:
  


Current Time: Sat Dec 10 14:50:18 CST 2016

Total time taken to generate the page: 0.29880 seconds