OraFAQ Forum: SQL & PL/SQL » Row number for null

Home » SQL & PL/SQL » SQL & PL/SQL » Row number for null (10g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Row number for null [message #606855]

Thu, 30 January 2014 01:54

mamalik
Messages: 268
Registered: November 2008
Location: Pakistan

Senior Member

Dear All,

I have follwoing table

create table ABC(trn_ide number,trn_Val VARCHAR2(250));
Insert into ABC   (TRN_IDE, TRN_VAL) Values   (1, 'A');
Insert into ABC   (TRN_IDE) Values   (2);
Insert into ABC   (TRN_IDE, TRN_VAL) Values   (3, 'A');
Insert into ABC   (TRN_IDE, TRN_VAL) Values   (4, 'A');
Insert into ABC   (TRN_IDE) Values   (5);
Insert into ABC   (TRN_IDE) Values   (6);
Insert into ABC   (TRN_IDE) Values   (7);
Insert into ABC   (TRN_IDE) Values   (8);
Insert into ABC   (TRN_IDE, TRN_VAL) Values   (9, 'F');
Insert into ABC   (TRN_IDE) Values   (10);

I write Select * From ABC Order By Trn_Ide and i get

TRN_IDE	TRN_VAL

1	A
2	
3	A
4	A
5	
6	
7	
8	
9	F
10

I need a column serial_number which should show consective serial number where trn_Val is null. My required output is

TRN_IDE	TRN_VAL       Serial_Number

1	A
2	                1
3	A
4	A
5	                2
6	                3
7	                4
8	                5
9	F
10	                6

Bundle of thanks for your time.

Regards,
Asif.

Report message to a moderator

Re: Row number for null [message #606858 is a reply to message #606855]

Thu, 30 January 2014 02:23

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

SQL> select trn_ide, trn_val, 
  2         nvl2(trn_val, to_number(null),
  3              count(nvl2(trn_val,null,'a')) over (order by trn_ide))
  4           serial_number
  5  from abc
  6  order by trn_ide
  7  /
   TRN_IDE TRN_VAL SERIAL_NUMBER
---------- ------- -------------
         1 A
         2                     1
         3 A
         4 A
         5                     2
         6                     3
         7                     4
         8                     5
         9 F
        10                     6

Report message to a moderator

Re: Row number for null [message #606859 is a reply to message #606858]

Thu, 30 January 2014 02:27

Littlefoot
Messages: 21808
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Similarly, just uglier than Michel's suggestion:

SQL>   SELECT trn_ide,
  2           trn_val,
  3           DECODE (
  4              trn_val,
  5              NULL, ROW_NUMBER ()
  6                    OVER (PARTITION BY DECODE (trn_val, NULL, 1, 2)
  7                          ORDER BY trn_ide),
  8              NULL)
  9              serial_number
 10      FROM abc
 11  ORDER BY trn_ide;

   TRN_IDE TRN_VAL    SERIAL_NUMBER
---------- ---------- -------------
         1 A
         2                        1
         3 A
         4 A
         5                        2
         6                        3
         7                        4
         8                        5
         9 F
        10                        6

10 rows selected.

SQL>

Report message to a moderator

Re: Row number for null [message #606864 is a reply to message #606859]

Thu, 30 January 2014 03:24

mamalik
Messages: 268
Registered: November 2008
Location: Pakistan

Senior Member

Great query by great peoples. Smile

Report message to a moderator

Re: Row number for null [message #606867 is a reply to message #606859]

Thu, 30 January 2014 04:55

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

Seems even uglier, but it would suffice :

SQL> SELECT TRN_IDE, TRN_VAL, NULL SERIAL_NUMBER
  2    FROM ABC
  3   WHERE TRN_VAL IS NOT NULL
  4  UNION ALL
  5  SELECT TRN_IDE, TRN_VAL, ROW_NUMBER() OVER(ORDER BY TRN_IDE)
  6    FROM (SELECT TRN_IDE, TRN_VAL FROM ABC WHERE TRN_VAL IS NULL)
  7   ORDER BY TRN_IDE;
 
   TRN_IDE TRN_VAL     SERIAL_NUMBER
---------- ----------- -------------
         1 A                                                                                
         2             1
         3 A                                                                                
         4 A                                                                                
         5             2
         6             3
         7             4
         8             5
         9 F                                                                                
        10             6
 
10 rows selected

Report message to a moderator

Re: Row number for null [message #606896 is a reply to message #606855]

Thu, 30 January 2014 09:07

Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA

Senior Member

SCOTT@pdborcl12 > select  abc.*,
  2                       sum(nvl2(trn_val,null,1)) over(
  3                                                      partition by nvl2(trn_val,1,2)
  4                                                      order by trn_ide
  5                                                     ) serial_numbe
  6                 from  abc
  7                 order by trn_ide
  8  /

   TRN_IDE TRN_VAL    SERIAL_NUMBE
---------- ---------- ------------
         1 A
         2                       1
         3 A
         4 A
         5                       2
         6                       3
         7                       4
         8                       5
         9 F
        10                       6

10 rows selected.

SCOTT@pdborcl12 >

SY.

Report message to a moderator

Re: Row number for null [message #606912 is a reply to message #606896]

Thu, 30 January 2014 11:45

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

@SY - Though I did not check the explain plan, however, I believe, the optimizer would consider your's and Michel's query equally efficient. May be I could validate it tomorrow Smile

Report message to a moderator

Re: Row number for null [message #606915 is a reply to message #606912]

Thu, 30 January 2014 11:58

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

I have nothing to post but I nevertheless post to say I have nothing to post. Smile

Report message to a moderator

Re: Row number for null [message #606918 is a reply to message #606915]

Thu, 30 January 2014 14:40

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

Michel Cadot wrote on Thu, 30 January 2014 23:28

I have nothing to post but I nevertheless post to say I have nothing to post.

Wow, a great post by the greatest person, as usually, everybody says, normal behavior was NOT expected from Mr. Cadot.

Anyway, this is what I said(if you are sane enough to understand) :

Your query and it's explain plan :

SQL> explain plan for select trn_ide, trn_val, nvl2(trn_val, to_number(null), count(nvl2(trn_val,null,'a')) over (order by trn_ide))
     serial_number from abc order by trn_ide;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

Plan hash value: 361826208

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    10 |  1400 |     3   (0)| 00:00:01 |
|   1 |  WINDOW SORT       |      |    10 |  1400 |     3   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| ABC  |    10 |  1400 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

Note

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

-----
   - dynamic sampling used for this statement (level=2)

13 rows selected.

Now SY's query and explain plan :

SQL> explain plan for select  abc.*, sum(nvl2(trn_val,null,1)) over(partition by nvl2(trn_val,1,2) order by trn_ide) serial_numbe
     from abc order by trn_ide;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

Plan hash value: 2320178911

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |    10 |  1400 |     5  (40)| 00:00:01 |
|   1 |  SORT ORDER BY      |      |    10 |  1400 |     5  (40)| 00:00:01 |
|   2 |   WINDOW SORT       |      |    10 |  1400 |     5  (40)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| ABC  |    10 |  1400 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=2)

14 rows selected.

Now Mr. Cadot, pay attention to the second explain plan, do you see this extra sorting task, which is actually an overhead :

|   1 |  SORT ORDER BY      |      |    10 |  1400 |     5  (40)| 00:00:01 |

It could be avoided if YOUR query is used.

Report message to a moderator

Re: Row number for null [message #606921 is a reply to message #606918]

Thu, 30 January 2014 14:47

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

Wow, a great post by the greatest person, as usually, everybody says, normal behavior was NOT expected from Mr. Cadot.
Anyway, this is what I said(if you are sane enough to understand) :

Personal attacks and insults lead to banishment. Razz

[Updated on: Fri, 31 January 2014 01:14]

Report message to a moderator

Previous Topic:	creation_date should be equal to update_date
Next Topic:	exceptions

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 19:25:16 CDT 2024