asked on

help with sql

Oracle 10g

I have a table that looks like below:

ID user status
1 10
1 20
1 30
2 10
2 20
2 xyz 30
3 10
3 20
3 30

Basically the user will be blank mostly. I want to select where status=20. But if there is a value for the user then it should not look at the 'status' and should select the records where there is a value for 'User' regardless what status is..

Basically my select should give the below 2 records:

ID USER status
1 20
2 xyz 30
3 20

Can you please let me know how to do this in SQL..

Thanks

Sharath S

try this.

select *
  from your_table
 where user is not null or status = 20;

Open in new window

gs79

ASKER

that will not work. It gives the following result There will be two records for ID=2. I want only one record for ID = 2 where usr = 'xyz'

ID      USR      STATUS
1             20
2             20
2      xyz        30
3             20

slightwv (䄆 Netminder)

I'm thinking it should be a lot easier than this but this is what I have off the top of my head:

select id, user_name, status from
(
select id, user_name, status, row_number() over(partition by id order by case when user_name is null then 1 else 0 end, case when status=20 then 0 else 1 end) myrownum
from tab1 where status=20 or user_name is not null
)
where myrownum = 1
/

ASKER CERTIFIED SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Peter Chan

Hi GS,
Try this
with set1 as
(select id, user, status, row_number() over (partition by id order by status) as rn
from tab1)
select id, user, status
from set1
where user is not null or (user is null and rn=2);

slightwv (䄆 Netminder)

HuaMinChen,

I don't think your SQL will work. Based on my test case and your SQL, I get both rows with id=2.

There is also a missing aspect to the test data: An id with a username set where the status i less than 20.

Here is my test setup and running your SQL, I don't get the stated results.

Based on the requirements and the below test I take the expected results to be:

ID USER_NAME STATUS
---------- ---------- ----------
1 20
2 xyz 30
3 20
5 q 10

My SQL provides these results.

drop table tab1 purge;
create table tab1( ID number, user_name varchar2(10), status number);
insert into tab1 values(1,null,10);
insert into tab1 values(1,null,20);
insert into tab1 values(1,null,30);
insert into tab1 values(2,null,10);
insert into tab1 values(2,null,20);
insert into tab1 values(2,'xyz',30);
insert into tab1 values(3,null,10);
insert into tab1 values(3,null,20);
insert into tab1 values(3,null,30);
insert into tab1 values(4,null,30);
insert into tab1 values(5,'q',10);
insert into tab1 values(5,null,20);
commit;

with set1 as
(select id, user_name, status, row_number() over (partition by id order by status) as rn
from tab1)
select id, user_name, status
from set1
where user_name is not null or (user_name is null and rn=2);

Open in new window

SOLUTION

awking00

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

gs79

ASKER

I ended up implementing slightwv solution. I think awking00 solution is an improvisation as it avoids case statement within analytic function..

Thanks for the responses..