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
gs79Asked:
Who is Participating?
 
slightwv (䄆 Netminder)Connect With a Mentor Commented:
Don't need the second case:


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) myrownum
      from tab1 where status=20 or user_name is not null
)
where myrownum = 1
/
0
 
SharathData EngineerCommented:
try this.
select *
  from your_table
 where user is not null or status = 20;

Open in new window

0
 
gs79Author Commented:
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
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
slightwv (䄆 Netminder) Commented:
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
/
0
 
HuaMin ChenSystem AnalystCommented:
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);
0
 
slightwv (䄆 Netminder) Commented:
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

0
 
awking00Connect With a Mentor Commented:
select id, user_name, status from
(select id, user_name, status, row_number() over (partition by id order by user_name) rn
 from tab1
 where status = 20 or user_name is not null)
where rn = 1;
0
 
gs79Author Commented:
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..
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.