Re: Difficult Query Problem

On 2005-11-03, tal fts <tal.fts_at_gmail.com> wrote:
> Hi
>
> i have a query which returns me an xml of the rows using xmlagg
> i want to limit the rows it return , which is not the problem (using
> rownum < num)
>
> the problem is that i want to combine in the same sql , the real rows
> count using count(*)
> like if i didnt added the rownum < num)
>
> the result should be a count field with the real rowscount of the query
> and an xml of the first [num] rows
>
> is it possible?, right no i am doing 2 sepearte quries, which is a
> waste of time

As always, when asking SQL related questions, please post executable create table and insert statements as well as what you have already tried so far.
Anyway, this should do:

create table xml_t (t xmltype);

insert into xml_t values (xmltype('<a><b>one</b><c>two</c></a>'));
insert into xml_t values (xmltype('<a><b>ten</b><c>twenty</c></a>'));
insert into xml_t values (xmltype('<a><b>hundred</b><c>two hundred</c></a>'));
insert into xml_t values (xmltype('<a><b>dog</b><c>cat</c></a>'));
insert into xml_t values (xmltype('<a><b>bird</b><c>egg</c></a>'));

select xmlagg(case when rownum <= 3 then t else null end), count(*) from xml_t;

drop table xml_t;

hth
Rene

-- 
  Rene Nyffenegger
  http://www.adp-gmbh.ch/