[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 526
  • Last Modified:

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
0
gs79
Asked:
gs79
2 Solutions
 
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
 
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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
slightwv (䄆 Netminder) 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
 
HuaMinChenBusiness 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
 
awking00Commented:
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

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now