Ignore row if one of the Rcodes is null

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Ignore row if one of the Rcodes is null

Author

Topic

plsqldev
Starting Member

11 Posts

Posted - 2011-07-21 : 21:31:26

I have a data set

Num RCode Date Code MaxCode
12 'R1' '12/1/2010' 9 10
12 NULL '12/1/2010' 10 10
13 'R8' '12/1/2010' 11 11
13 NULL '12/1/2010' 8 11
14 'S1' '10/1/2010' 11 12
14 'S2' '10/1/2010' 12 12
15 'A1' '5/1/2011' 10 13
15 'A1' '5/1/2011' 10 13
16 'P1' '4/1/2011' 8 13
16 'NULL '4/1/2011' 8 13
17 'NULL '1/1/2011' 3 3
18 'S7' '1/1/2011' 3 9
19 'S1' '1/1/2011' 9 9
19 'S2' '1/1/2011' 9 9

I need below as output...

Num RCode Date Code MaxCode
12 R1 12/1/2010 9 10
13 R8 12/1/2010 11 11
14 S1 10/1/2010 11 12
14 S2 10/1/2010 12 12
15 A1 5/1/2011 10 13
15 A1 5/1/2011 10 13
16 P1 4/1/2011 8 13
17 NULL 1/1/2011 3 3
18 S7 1/1/2011 3 9
19 S1 1/1/2011 9 9
19 S2 1/1/2011 9 9

Can any one help me.

Thank you

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-07-21 : 22:05:39

[code]
select *
from (
select *, r = dense_rank() over (partition by Num order by case when RCode is not null then 1 else 2 end)
from yourtable
) t
where t.r =1
[/code]

KH
[spoiler]Time is always against us[/spoiler]

plsqldev
Starting Member

11 Posts

Posted - 2011-07-22 : 08:05:27

It will eliminate Num 17 data also roght? I need that recored...
If the row count partition by num>=2 and one of the rcodes is null then I need to filter that row.

and also If the row count of num is >=2 and all the rcodes for that Num are all nulls then I need that record.

for example.. 17 has 1 record with null value. I need that data.
16,12,13 have 2 records with one not null value. and also for num 19 - if count(Num) >=2 and both RCodes are null.. I need Null as out put

here is the data set that I have

12 'R1' '12/1/2010' 9 10
12 NULL '12/1/2010' 10 10
13 'R8' '12/1/2010' 11 11
13 NULL '12/1/2010' 8 11
14 'S1' '10/1/2010' 11 12
14 'S2' '10/1/2010' 12 12
16 'P1' '4/1/2011' 8 13
16 'NULL '4/1/2011' 8 13
17 'NULL '1/1/2011' 3 3
18 'S7' '1/1/2011' 3 9
19 NULL 1/12011 1 2
19 NULL 1/1/2011 2 2

I need Output as
12 'R1' '12/1/2010' 9 10
13 'R8' '12/1/2010' 11 11
14 'S1' '10/1/2010' 11 12
14 'S2' '10/1/2010' 12 12
16 'P1' '4/1/2011' 8 13
17 'NULL '1/1/2011' 3 3
18 'S7' '1/1/2011' 3 9
19 NULL 1/12011 2 2

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-07-22 : 09:00:56

From looking at KH's code it looks like you WILL get a row for NUM = 17. His logic is that, if there is an RCode that is NOT NULL, pick that up. If there is no non-null RCode for a given Num, then it would pick up the one with the NULL.

plsqldev
Starting Member

11 Posts

Posted - 2011-07-22 : 09:40:20

Sorry about that... It was my mistake. I just tried.. it is working as expected. Thanks for you help.
I really apprecuate that... Thank you agin.

plsqldev
Starting Member

11 Posts

Posted - 2011-07-22 : 12:48:17

your code works as expected
except one scenario... If both the rcodes are null (for 19) it still showing two records... I need one record only with Code 2 and MaxCode 2. Can you please provide me a solution.

Thank you

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-07-22 : 13:11:25

KH is in Singapore, where it is 1:10 in the morning now. So unless he is an "In (Som, Ni, Yak)" as he claims to be, he is probably sleeping, so I will try:

Change the dense_rank() to row_number()

Edit: Hold off on that, because then you will lose the two rows that you wanted for 15,A1.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-07-22 : 13:23:12

If this does not work, post back, I will test and post working code:

select   *
from   (
      select   *, 
         r = dense_rank() over (partition by Num order by case when RCode is not null then 1 else 2 end),
         r2 = row_number() over (partition by Num order by case when RCode is not null then 1 else 2 end)

      from   yourtable
   ) t
where   (t.r =1 AND t.RCode IS NOT NULL) OR t.r2=1

plsqldev
Starting Member

11 Posts

Posted - 2011-07-22 : 22:44:27

It is working.. Thank ypu for the solution. I really appreciate your help.

Thank you so munch

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-07-23 : 05:47:03

quote:
Originally posted by sunitabeck

KH is in Singapore, where it is 1:10 in the morning now. So unless he is an "NOT In (Som, Ni, Yak)" as he claims to be, he is probably sleeping, so I will try:

Change the dense_rank() to row_number()

Edit: Hold off on that, because then you will lose the two rows that you wanted for 15,A1.

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources