start stop rowcount in query [message #428683] |
Thu, 29 October 2009 08:16 |
gtriant
Messages: 42 Registered: September 2006
|
Member |
|
|
Hello.
I need to produce a table that in the first column there will be a counter. However this counter will be stopped for a row of dashes and then continued. The rows of the tables before and after the dashes come from different queries using union.
Example:
# Name Surname
1 Jack Black
2 Greg Green
- - -
3 Nick Brown
4 Jake White
- - -
5 Mary Lou
etc.
The above can continue....
So the question is how can i stop and start the counting.
IT MUST BE DONE BY A SINGLE QUERY (not a script!).
I cannot do any addition or alteration to the database (no function, no new sequence etc)!!!
What i have done so far is sth like this:
select to_char(rownum), name, surname from table1 where ...
union all
select '-', '-', '-' from dual
union all
select to_char(rownum +
(select count(*) from table1 where ...)
), name, surname from table2 where ...
The above works, but i cant keep adding the record counts of all the above unions forever!!!!
Anything better?????
|
|
|
Re: start stop rowcount in query [message #428690 is a reply to message #428683] |
Thu, 29 October 2009 08:38 |
cookiemonster
Messages: 13960 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
Actually the above isn't guaranteed to work. Oracle could reorder the data set and there is no order by clause you could use to stop it.
This is a formatting problem - pure SQL isn't really designed to deal with formatting problems - that's for the client program that displays the data to sort out.
What program are you using to display the data?
|
|
|
Re: start stop rowcount in query [message #428695 is a reply to message #428683] |
Thu, 29 October 2009 08:51 |
|
Michel Cadot
Messages: 68718 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
select rownum, name, surname
from ( select 1 tabnb, name, surname from tablea where ...
union all
select 1.5, '-', '-', '-' from dual
union all
select 2, name, surname from tableb where ...
order by 1 )
/
Regards
Michel
[Updated on: Thu, 29 October 2009 08:51] Report message to a moderator
|
|
|
|
Re: start stop rowcount in query [message #428798 is a reply to message #428792] |
Fri, 30 October 2009 02:16 |
|
Michel Cadot
Messages: 68718 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
It was just a first and quick draft. It is easy to fix it in what you want for instance (next time post a test case):
SQL> select decode(ename,
2 '-',to_number(null),
3 sum(decode(ename,'-',0,1))
4 over(order by ord rows between unbounded preceding and current row)
5 ) rn,
6 ename
7 from ( select 1 ord, ename from emp where rownum<=3
8 union all
9 select 1.5, '-' from dual
10 union all
11 select 2, ename from emp where rownum<=5
12 )
13 order by ord
14 /
RN ENAME
---------- ----------
1 SMITH
2 ALLEN
3 WARD
-
4 SMITH
5 ALLEN
6 WARD
7 JONES
8 MARTIN
Regards
Michel
[Updated on: Fri, 30 October 2009 02:17] Report message to a moderator
|
|
|
|