Home » SQL & PL/SQL » SQL & PL/SQL » Query help (11.0.1)
Query help [message #574384] Wed, 09 January 2013 16:48 Go to next message
deepa_balu
Messages: 73
Registered: March 2005
Member
I need a select statement for the output like following
1
1
1
1
2
2
2
2
3
3
3
3

likewise the count should be increasing for every four records.
Please help

[Updated on: Wed, 09 January 2013 16:53]

Report message to a moderator

Re: Query help [message #574386 is a reply to message #574384] Wed, 09 January 2013 18:25 Go to previous messageGo to next message
dariyoosh
Messages: 532
Registered: March 2009
Location: Iran / France
Senior Member
As I understand, it seems that you need a row generator for this problem (given the fact that you don't give a clear description of your table and your test case).

Maybe the following could give you an idea


VARIABLE block_size NUMBER;
VARIABLE number_of_blocks NUMBER;
BEGIN
    :block_size := 4;           -- according to the OP's specification
    :number_of_blocks := 6;     -- it's just an example that I chose
END;
/
WITH block_generator AS
(
    SELECT COLUMN_VALUE AS colval
    FROM    TABLE
            (
                CAST
                (
                    MULTISET
                    (
                        SELECT LEVEL 
                        FROM DUAL 
                        CONNECT BY LEVEL <= :block_size
                    ) 
                    AS SYS.odciNumberList
                )
            )
),
row_generator AS
(
    SELECT COLUMN_VALUE AS colval
    FROM    TABLE
            (
                CAST
                (
                    MULTISET
                    (
                        SELECT LEVEL 
                        FROM DUAL 
                        CONNECT BY LEVEL <= :number_of_blocks
                    ) 
                    AS SYS.odciNumberList
                )
            )
)
SELECT t2.colval
FROM block_generator t1 CROSS JOIN row_generator t2
ORDER BY t2.colval ASC;


Which gives the following result

    COLVAL
----------
	 1
	 1
	 1
	 1
	 2
	 2
	 2
	 2
	 3
	 3
	 3
	 3
	 4
	 4
	 4
	 4
	 5
	 5
	 5
	 5
	 6
	 6
	 6
	 6

24 rows selected.

SQL> 




Regards,
Dariyoosh
Re: Query help [message #574400 is a reply to message #574386] Thu, 10 January 2013 00:08 Go to previous messageGo to next message
Littlefoot
Messages: 19476
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Well, yes, that's a high-tech solution. Here's a simple one.
SQL> select a.lvl
  2  from (select level lvl
  3        from dual
  4        connect by level <= 6  -- number of blocks
  5       ) a,
  6       (select level lvl
  7        from dual
  8        connect by level <= 4  -- block size
  9       ) b;

       LVL
----------
         1
         1
         1
         1
         2
         2
         2
         2
         3
         3
         3
         3
         4
         4
         4
         4
         5
         5
         5
         5
         6
         6
         6
         6

24 rows selected.

SQL>

[Updated on: Thu, 10 January 2013 00:09]

Report message to a moderator

Re: Query help [message #574404 is a reply to message #574400] Thu, 10 January 2013 01:14 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
with some twist

 SELECT TRUNC((LEVEL+3)/4) COL
   FROM DUAL
CONNECT BY LEVEL <= 15

       COL
----------
         1
         1
         1
         1
         2
         2
         2
         2
         3
         3
         3
         3
         4
         4
         4


regards,
Delna
Re: Query help [message #574405 is a reply to message #574404] Thu, 10 January 2013 01:18 Go to previous message
Littlefoot
Messages: 19476
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Cool! ./fa/2115/0/
Previous Topic: Delete records from child and parent table [merged 2 by jd]
Next Topic: SQL%ROWCOUNT USing in INSERT or update trigger
Goto Forum:
  


Current Time: Sat Aug 23 03:28:34 CDT 2014

Total time taken to generate the page: 0.08267 seconds