Home » SQL & PL/SQL » SQL & PL/SQL » SQL Help (Oracle 9.2.0.8)
| SQL Help [message #326259] |
Tue, 10 June 2008 22:10  |
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 #326272 is a reply to message #326266] |
Tue, 10 June 2008 23:25  |
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 #326331 is a reply to message #326259] |
Wed, 11 June 2008 02:07 |
|
|
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 #326529 is a reply to message #326331] |
Wed, 11 June 2008 22:14 |
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 #326650 is a reply to message #326529] |
Thu, 12 June 2008 02:57 |
|
|
| 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 . ..
|
|
|
|
|
|
Goto Forum:
Current Time: Fri Dec 06 18:24:32 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
