## Question and Answer

## You Asked

Hi!

I found your post on one of the newsgroup in which you described how to write user defined SUM aggregate for new interval type. You also provided code for "day_to_second_sum_type".

In my work I need SUM and AVG aggregates for interval type with precision 6 for days and 6 for seconds - so "interval day(6) to second(6)" is the type I'm interested in.

I changed your code so that "total interval day to second" is

"total interval day(6) to second(6)" now. However I got problems with ODCIAggregateTerminate - returnValue which is the output parameter has to small precision hence I receive error while executing aggregate.

While iterating everything works fine until invoking terminate function. Is there any way to workaround this precision limitations??

Could you also give me a hint how to build AVG aggregate?

And one more thing - Let's say I have a table which contains two colums - Name and birth date;

How to group all the rows on daily or yearly or monthly basis.

How to assign specific group number to each row???

Thank You very much!

Marcin Belczewski

marcin.belczewski@globalintech.pl

I found your post on one of the newsgroup in which you described how to write user defined SUM aggregate for new interval type. You also provided code for "day_to_second_sum_type".

In my work I need SUM and AVG aggregates for interval type with precision 6 for days and 6 for seconds - so "interval day(6) to second(6)" is the type I'm interested in.

I changed your code so that "total interval day to second" is

"total interval day(6) to second(6)" now. However I got problems with ODCIAggregateTerminate - returnValue which is the output parameter has to small precision hence I receive error while executing aggregate.

While iterating everything works fine until invoking terminate function. Is there any way to workaround this precision limitations??

Could you also give me a hint how to build AVG aggregate?

And one more thing - Let's say I have a table which contains two colums - Name and birth date;

How to group all the rows on daily or yearly or monthly basis.

How to assign specific group number to each row???

Thank You very much!

Marcin Belczewski

marcin.belczewski@globalintech.pl

## and Tom said...

Ok, it won't work without using base SQL datatypes -- so thats what we'll do. We'll just have to use a character string to send and get results is all. Here we go -- a SUM and AVG routine for day to second intervals for Oracle9i:

ops$tkyte@ORA920.US.ORACLE.COM> create or replace type day_to_second_sum_type as object

2 (

3 total interval day(9) to second(9),

4

5 static function

6 ODCIAggregateInitialize(sctx IN OUT day_to_second_sum_type )

7 return number,

8

9 member function

10 ODCIAggregateIterate(self IN OUT day_to_second_sum_type ,

11 value IN varchar2 )

12 return number,

13

14 member function

15 ODCIAggregateTerminate(self IN day_to_second_sum_type,

16 returnValue OUT varchar2,

17 flags IN number)

18 return number,

19

20 member function

21 ODCIAggregateMerge(self IN OUT day_to_second_sum_type,

22 ctx2 IN day_to_second_sum_type)

23 return number

24 );

25 /

Type created.

ops$tkyte@ORA920.US.ORACLE.COM>

ops$tkyte@ORA920.US.ORACLE.COM> create or replace type body day_to_second_sum_type

2 is

3

4 static function ODCIAggregateInitialize(sctx IN OUT day_to_second_sum_type)

5 return number

6 is

7 begin

8 sctx := day_to_second_sum_type( numtodsinterval( 0, 'SECOND' ) );

9 return ODCIConst.Success;

10 end;

11

12 member function ODCIAggregateIterate(self IN OUT day_to_second_sum_type,

13 value IN varchar2 )

14 return number

15 is

16 l_value interval day(9) to second(9);

17 begin

18 l_value := value;

19 dbms_output.put_line( l_value );

20 self.total := self.total + l_value;

21 return ODCIConst.Success;

22 end;

23

24 member function ODCIAggregateTerminate(self IN day_to_second_sum_type,

25 returnValue OUT varchar2,

26 flags IN number)

27 return number

28 is

29 begin

30 returnValue := self.total;

31 return ODCIConst.Success;

32 end;

33

34 member function ODCIAggregateMerge(self IN OUT day_to_second_sum_type,

35 ctx2 IN day_to_second_sum_type)

36 return number

37 is

38 begin

39 self.total := self.total + ctx2.total;

40 return ODCIConst.Success;

41 end;

42 end;

43 /

Type body created.

ops$tkyte@ORA920.US.ORACLE.COM> show err

No errors.

ops$tkyte@ORA920.US.ORACLE.COM>

ops$tkyte@ORA920.US.ORACLE.COM>

ops$tkyte@ORA920.US.ORACLE.COM> CREATE or replace

2 FUNCTION ds_sum(input varchar2 )

3 RETURN varchar2

4 PARALLEL_ENABLE AGGREGATE USING day_to_second_sum_type;

5 /

Function created.

ops$tkyte@ORA920.US.ORACLE.COM>

ops$tkyte@ORA920.US.ORACLE.COM> set echo on

ops$tkyte@ORA920.US.ORACLE.COM>

ops$tkyte@ORA920.US.ORACLE.COM> create or replace type day_to_second_avg_type as object

2 (

3 total interval day(9) to second(9),

4 obs number,

5

6 static function

7 ODCIAggregateInitialize(sctx IN OUT day_to_second_avg_type )

8 return number,

9

10 member function

11 ODCIAggregateIterate(self IN OUT day_to_second_avg_type ,

12 value IN varchar2 )

13 return number,

14

15 member function

16 ODCIAggregateTerminate(self IN day_to_second_avg_type,

17 returnValue OUT varchar2,

18 flags IN number)

19 return number,

20

21 member function

22 ODCIAggregateMerge(self IN OUT day_to_second_avg_type,

23 ctx2 IN day_to_second_avg_type)

24 return number

25 );

26 /

Type created.

ops$tkyte@ORA920.US.ORACLE.COM> show errors

No errors.

ops$tkyte@ORA920.US.ORACLE.COM> pause

ops$tkyte@ORA920.US.ORACLE.COM>

ops$tkyte@ORA920.US.ORACLE.COM> create or replace type body day_to_second_avg_type

2 is

3

4 static function ODCIAggregateInitialize(sctx IN OUT day_to_second_avg_type)

5 return number

6 is

7 begin

8 sctx := day_to_second_avg_type( numtodsinterval( 0, 'SECOND' ), 0 );

9 return ODCIConst.Success;

10 end;

11

12 member function ODCIAggregateIterate(self IN OUT day_to_second_avg_type,

13 value IN varchar2 )

14 return number

15 is

16 l_value interval day(9) to second(9);

17 begin

18 l_value := value;

19 self.total := self.total + l_value;

20 self.obs := self.obs + 1;

21 return ODCIConst.Success;

22 end;

23

24 member function ODCIAggregateTerminate(self IN day_to_second_avg_type,

25 returnValue OUT varchar2,

26 flags IN number)

27 return number

28 is

29 begin

30 if ( self.obs > 0 )

31 then

32 returnValue := self.total/self.obs;

33 else

34 returnValue := NULL;

35 end if;

36 return ODCIConst.Success;

37 end;

38

39 member function ODCIAggregateMerge(self IN OUT day_to_second_avg_type,

40 ctx2 IN day_to_second_avg_type)

41 return number

42 is

43 begin

44 self.total := self.total + ctx2.total;

45 self.obs := self.obs + ctx2.obs;

46 return ODCIConst.Success;

47 end;

48 end;

49 /

Type body created.

ops$tkyte@ORA920.US.ORACLE.COM> show errors

No errors.

ops$tkyte@ORA920.US.ORACLE.COM> pause

ops$tkyte@ORA920.US.ORACLE.COM>

ops$tkyte@ORA920.US.ORACLE.COM>

ops$tkyte@ORA920.US.ORACLE.COM> CREATE or replace

2 FUNCTION ds_avg(input varchar2 )

3 RETURN varchar2

4 PARALLEL_ENABLE AGGREGATE USING day_to_second_avg_type;

5 /

Function created.

ops$tkyte@ORA920.US.ORACLE.COM>

ops$tkyte@ORA920.US.ORACLE.COM> create table t ( x interval day(9) to second(9) );

Table created.

ops$tkyte@ORA920.US.ORACLE.COM>

ops$tkyte@ORA920.US.ORACLE.COM> insert into t

2 select numtodsinterval( rownum*1000, 'hour' )

3 from all_objects

4 where rownum < 25;

24 rows created.

ops$tkyte@ORA920.US.ORACLE.COM>

ops$tkyte@ORA920.US.ORACLE.COM> select to_dsinterval( ds_avg(x) ) avg,

2 to_dsinterval( ds_sum(x) ) sum

3 from t;

AVG

---------------------------------------------------------------------------

SUM

---------------------------------------------------------------------------

+000000520 19:59:59.999999999

+000012500 00:00:00.000000000

ops$tkyte@ORA920.US.ORACLE.COM>

ops$tkyte@ORA920.US.ORACLE.COM> create or replace type day_to_second_sum_type as object

2 (

3 total interval day(9) to second(9),

4

5 static function

6 ODCIAggregateInitialize(sctx IN OUT day_to_second_sum_type )

7 return number,

8

9 member function

10 ODCIAggregateIterate(self IN OUT day_to_second_sum_type ,

11 value IN varchar2 )

12 return number,

13

14 member function

15 ODCIAggregateTerminate(self IN day_to_second_sum_type,

16 returnValue OUT varchar2,

17 flags IN number)

18 return number,

19

20 member function

21 ODCIAggregateMerge(self IN OUT day_to_second_sum_type,

22 ctx2 IN day_to_second_sum_type)

23 return number

24 );

25 /

Type created.

ops$tkyte@ORA920.US.ORACLE.COM>

ops$tkyte@ORA920.US.ORACLE.COM> create or replace type body day_to_second_sum_type

2 is

3

4 static function ODCIAggregateInitialize(sctx IN OUT day_to_second_sum_type)

5 return number

6 is

7 begin

8 sctx := day_to_second_sum_type( numtodsinterval( 0, 'SECOND' ) );

9 return ODCIConst.Success;

10 end;

11

12 member function ODCIAggregateIterate(self IN OUT day_to_second_sum_type,

13 value IN varchar2 )

14 return number

15 is

16 l_value interval day(9) to second(9);

17 begin

18 l_value := value;

19 dbms_output.put_line( l_value );

20 self.total := self.total + l_value;

21 return ODCIConst.Success;

22 end;

23

24 member function ODCIAggregateTerminate(self IN day_to_second_sum_type,

25 returnValue OUT varchar2,

26 flags IN number)

27 return number

28 is

29 begin

30 returnValue := self.total;

31 return ODCIConst.Success;

32 end;

33

34 member function ODCIAggregateMerge(self IN OUT day_to_second_sum_type,

35 ctx2 IN day_to_second_sum_type)

36 return number

37 is

38 begin

39 self.total := self.total + ctx2.total;

40 return ODCIConst.Success;

41 end;

42 end;

43 /

Type body created.

ops$tkyte@ORA920.US.ORACLE.COM> show err

No errors.

ops$tkyte@ORA920.US.ORACLE.COM>

ops$tkyte@ORA920.US.ORACLE.COM>

ops$tkyte@ORA920.US.ORACLE.COM> CREATE or replace

2 FUNCTION ds_sum(input varchar2 )

3 RETURN varchar2

4 PARALLEL_ENABLE AGGREGATE USING day_to_second_sum_type;

5 /

Function created.

ops$tkyte@ORA920.US.ORACLE.COM>

ops$tkyte@ORA920.US.ORACLE.COM> set echo on

ops$tkyte@ORA920.US.ORACLE.COM>

ops$tkyte@ORA920.US.ORACLE.COM> create or replace type day_to_second_avg_type as object

2 (

3 total interval day(9) to second(9),

4 obs number,

5

6 static function

7 ODCIAggregateInitialize(sctx IN OUT day_to_second_avg_type )

8 return number,

9

10 member function

11 ODCIAggregateIterate(self IN OUT day_to_second_avg_type ,

12 value IN varchar2 )

13 return number,

14

15 member function

16 ODCIAggregateTerminate(self IN day_to_second_avg_type,

17 returnValue OUT varchar2,

18 flags IN number)

19 return number,

20

21 member function

22 ODCIAggregateMerge(self IN OUT day_to_second_avg_type,

23 ctx2 IN day_to_second_avg_type)

24 return number

25 );

26 /

Type created.

ops$tkyte@ORA920.US.ORACLE.COM> show errors

No errors.

ops$tkyte@ORA920.US.ORACLE.COM> pause

ops$tkyte@ORA920.US.ORACLE.COM>

ops$tkyte@ORA920.US.ORACLE.COM> create or replace type body day_to_second_avg_type

2 is

3

4 static function ODCIAggregateInitialize(sctx IN OUT day_to_second_avg_type)

5 return number

6 is

7 begin

8 sctx := day_to_second_avg_type( numtodsinterval( 0, 'SECOND' ), 0 );

9 return ODCIConst.Success;

10 end;

11

12 member function ODCIAggregateIterate(self IN OUT day_to_second_avg_type,

13 value IN varchar2 )

14 return number

15 is

16 l_value interval day(9) to second(9);

17 begin

18 l_value := value;

19 self.total := self.total + l_value;

20 self.obs := self.obs + 1;

21 return ODCIConst.Success;

22 end;

23

24 member function ODCIAggregateTerminate(self IN day_to_second_avg_type,

25 returnValue OUT varchar2,

26 flags IN number)

27 return number

28 is

29 begin

30 if ( self.obs > 0 )

31 then

32 returnValue := self.total/self.obs;

33 else

34 returnValue := NULL;

35 end if;

36 return ODCIConst.Success;

37 end;

38

39 member function ODCIAggregateMerge(self IN OUT day_to_second_avg_type,

40 ctx2 IN day_to_second_avg_type)

41 return number

42 is

43 begin

44 self.total := self.total + ctx2.total;

45 self.obs := self.obs + ctx2.obs;

46 return ODCIConst.Success;

47 end;

48 end;

49 /

Type body created.

ops$tkyte@ORA920.US.ORACLE.COM> show errors

No errors.

ops$tkyte@ORA920.US.ORACLE.COM> pause

ops$tkyte@ORA920.US.ORACLE.COM>

ops$tkyte@ORA920.US.ORACLE.COM>

ops$tkyte@ORA920.US.ORACLE.COM> CREATE or replace

2 FUNCTION ds_avg(input varchar2 )

3 RETURN varchar2

4 PARALLEL_ENABLE AGGREGATE USING day_to_second_avg_type;

5 /

Function created.

ops$tkyte@ORA920.US.ORACLE.COM>

ops$tkyte@ORA920.US.ORACLE.COM> create table t ( x interval day(9) to second(9) );

Table created.

ops$tkyte@ORA920.US.ORACLE.COM>

ops$tkyte@ORA920.US.ORACLE.COM> insert into t

2 select numtodsinterval( rownum*1000, 'hour' )

3 from all_objects

4 where rownum < 25;

24 rows created.

ops$tkyte@ORA920.US.ORACLE.COM>

ops$tkyte@ORA920.US.ORACLE.COM> select to_dsinterval( ds_avg(x) ) avg,

2 to_dsinterval( ds_sum(x) ) sum

3 from t;

AVG

---------------------------------------------------------------------------

SUM

---------------------------------------------------------------------------

+000000520 19:59:59.999999999

+000012500 00:00:00.000000000

ops$tkyte@ORA920.US.ORACLE.COM>

## Rating

Is this answer out of date? If it is, please let us know via a Comment

# Comments

You solved my problem - thanks!!

Tom,

Excellent examples above! Thanks Tom!

I'm using the avg function to help users manage their expectations of query performance. Id like to use Standard Deviation to show what portion of their query categories should fit with certain elapsed time groups. The sum and average calculations are quite simple after seeing how you did them. I'm trying to figure how to calculate the average, the count, AND iterate over the rows needed to calculate the standard deviation. The formula is simple: sqrt((summation of i=1:count(x(i)-average)^2) / (count-1)).

The count is needed for the average, and the average is needed for the iteration/summation of the variance portion. Do you have any suggestions for implementation?

Excellent examples above! Thanks Tom!

I'm using the avg function to help users manage their expectations of query performance. Id like to use Standard Deviation to show what portion of their query categories should fit with certain elapsed time groups. The sum and average calculations are quite simple after seeing how you did them. I'm trying to figure how to calculate the average, the count, AND iterate over the rows needed to calculate the standard deviation. The formula is simple: sqrt((summation of i=1:count(x(i)-average)^2) / (count-1)).

The count is needed for the average, and the average is needed for the iteration/summation of the variance portion. Do you have any suggestions for implementation?

I do not understand - why not just use stddev?

I'm storing an operation metric, user, version, end and start timestamps.

I used the to_dsinterval(ds_avg(end time - start time)) (see above) to show users the average times their operations take, and use max and min functions to show the bounds.

I'd like to show that for a particular type of metric operation, they should expect it to complete in X time interval 65% of the time, and within Y time interval 95% of the time, based on their empirical data.

My difficulty lies in STDDEV() expecting a numeric value or type implicitly convertable to numeric.

create table mytable (id number,

user_id varchar(35),

operation varchar(35)

app_version number,

end_time timestamp,

start_time timestamp);

Using the user-defined aggregate function and types defined above, I can do the

Select operation,

to_dsinterval(ds_avg(end_time - start_time)) "Average Elapsed Time",

max((end_time - start_time) "Max Elapsed Time",

min((end_time - start_time) "Min Elapsed Time"

from mytable

where

user_id = 'Some User' and

operation = 'Some Operation' and

app_version = 4.5;

I'd like to be able to get the standard deviation interval like this:

to_dsinterval(ds_stddev(end_time - start_time)) "Standard Deviation"

I've considered executing the query to get the sum and avg interval_type values, storing them in a user_context, for use in a user-defined aggregate. Am I overcomplicating this?

I used the to_dsinterval(ds_avg(end time - start time)) (see above) to show users the average times their operations take, and use max and min functions to show the bounds.

I'd like to show that for a particular type of metric operation, they should expect it to complete in X time interval 65% of the time, and within Y time interval 95% of the time, based on their empirical data.

My difficulty lies in STDDEV() expecting a numeric value or type implicitly convertable to numeric.

create table mytable (id number,

user_id varchar(35),

operation varchar(35)

app_version number,

end_time timestamp,

start_time timestamp);

Using the user-defined aggregate function and types defined above, I can do the

Select operation,

to_dsinterval(ds_avg(end_time - start_time)) "Average Elapsed Time",

max((end_time - start_time) "Max Elapsed Time",

min((end_time - start_time) "Min Elapsed Time"

from mytable

where

user_id = 'Some User' and

operation = 'Some Operation' and

app_version = 4.5;

I'd like to be able to get the standard deviation interval like this:

to_dsinterval(ds_stddev(end_time - start_time)) "Standard Deviation"

I've considered executing the query to get the sum and avg interval_type values, storing them in a user_context, for use in a user-defined aggregate. Am I overcomplicating this?

why not just munge the interval into a number - like "raw number of seconds between two points in time" or "number of minutes" or whatever.

This is great! But for ad hoc use on a database without those objects defined. Here is a simpler version:

numtodsinterval(avg(sysdate+interval_column*86400-sysdate), 'SECOND')

For detail, see http://kennethxu.blogspot.com/2009/04/converting-oracle-interval-data-type-to.html

numtodsinterval(avg(sysdate+interval_column*86400-sysdate), 'SECOND')

For detail, see http://kennethxu.blogspot.com/2009/04/converting-oracle-interval-data-type-to.html

What do you think about this function to get the difference in seconds between two intervals? It gets the fractional number of seconds from the actual interval difference then uses the logic of a previous poster to get the truncated number of days difference times 86400 to turn into seconds then adds the two pieces together.

The result of this can easily be sum/avg/whatever.

CREATE OR REPLACE

function elapsed_seconds_from_tms (p_start_tms timestamp, p_stop_tms timestamp)

return number

is

begin

return to_number(substr(to_char(p_stop_tms - p_start_tms),

instr(to_char(p_stop_tms - p_start_tms),'.',-1)))+

round((sysdate + (p_stop_tms - p_start_tms) - sysdate) *86400,0);

end;

/

The result of this can easily be sum/avg/whatever.

CREATE OR REPLACE

function elapsed_seconds_from_tms (p_start_tms timestamp, p_stop_tms timestamp)

return number

is

begin

return to_number(substr(to_char(p_stop_tms - p_start_tms),

instr(to_char(p_stop_tms - p_start_tms),'.',-1)))+

round((sysdate + (p_stop_tms - p_start_tms) - sysdate) *86400,0);

end;

/

... to get the difference in seconds between

two intervals? ...

I don't know about you - but I see no such function? I see one that does timestamps.

I'd just cast to dates and subtract, a lot easier.

two intervals? ...

I don't know about you - but I see no such function? I see one that does timestamps.

I'd just cast to dates and subtract, a lot easier.

Hi Tom,

I have a similar issue - I need to display dynamically a bunch of categories and I need the SQL algorithm to determine their size and names.

So, I have a table with one column and a variable number of records and I need to display them in GUI in pages, displaying also the page name which should be something like short name for first item to short name of last item in category.

Maximum items per page in 10 but I don't want to simply display blocks of 10 records. If the name of the 8th item starts with B and the name of the 9th item starts with C I want the first page to contain only the first 8 records. Minimum accepted items per page is 8, maximum is 10. The split should be based on the first 2 characters. Page name should be something like: first 4 chars of first item in category - first 4 chars of the last item in category.

Cand you help me with a SQL algorithm for this?

I have a similar issue - I need to display dynamically a bunch of categories and I need the SQL algorithm to determine their size and names.

So, I have a table with one column and a variable number of records and I need to display them in GUI in pages, displaying also the page name which should be something like short name for first item to short name of last item in category.

Maximum items per page in 10 but I don't want to simply display blocks of 10 records. If the name of the 8th item starts with B and the name of the 9th item starts with C I want the first page to contain only the first 8 records. Minimum accepted items per page is 8, maximum is 10. The split should be based on the first 2 characters. Page name should be something like: first 4 chars of first item in category - first 4 chars of the last item in category.

Cand you help me with a SQL algorithm for this?

this doesn't make sense. I didn't follow the logic here - no creates, no inserts, no example.

what if the first 11 all start with A?

what if the first 11 all start with A?

Test case:

create table fieldz(colz varchar2(30));

insert into fieldz values ('Revenues A');

insert into fieldz values ('Revenues B');

insert into fieldz values ('Revenues C');

insert into fieldz values ('Revenues D');

insert into fieldz values ('Revenues E');

insert into fieldz values ('Revenues F');

insert into fieldz values ('Revenues G');

insert into fieldz values ('Revenues H');

insert into fieldz values ('Sales A');

insert into fieldz values ('Sales B');

insert into fieldz values ('Sales C');

insert into fieldz values ('Sales D');

insert into fieldz values ('Sales E');

insert into fieldz values ('Sales F');

I don't want 2 pages of 10 items first and 5 items second. I want first page to contain first 8 records (starting with R) and the second page to start from the 9th record (starting with S).

If I had 9 records starting with R and 10th starting with S I would have wanted 9 records on first page.

create table fieldz(colz varchar2(30));

insert into fieldz values ('Revenues A');

insert into fieldz values ('Revenues B');

insert into fieldz values ('Revenues C');

insert into fieldz values ('Revenues D');

insert into fieldz values ('Revenues E');

insert into fieldz values ('Revenues F');

insert into fieldz values ('Revenues G');

insert into fieldz values ('Revenues H');

insert into fieldz values ('Sales A');

insert into fieldz values ('Sales B');

insert into fieldz values ('Sales C');

insert into fieldz values ('Sales D');

insert into fieldz values ('Sales E');

insert into fieldz values ('Sales F');

I don't want 2 pages of 10 items first and 5 items second. I want first page to contain first 8 records (starting with R) and the second page to start from the 9th record (starting with S).

If I had 9 records starting with R and 10th starting with S I would have wanted 9 records on first page.

and what if you have 11 records with R.

Or what if you had 8 with R, and 1 or 2 with S.

I do not see a SQL based approach to this, not readily in any case.

Or what if you had 8 with R, and 1 or 2 with S.

I do not see a SQL based approach to this, not readily in any case.

For information, same topic started in OraFAQ, at

http://www.orafaq.com/forum/mv/msg/163723/484237/102589/#msg_484237

Regards

Michel

You are asking your storage layer to do presentation layer items. SQL Queries do not return ASP pages. That's what your presentation layer is meant to do. Your query can be helpful by returning the items sorted alphabetically. Then your presentation layer can break the pages accordingly.

not true, not true at all.

SQL is not storage. SQL is a programming language. It can and should be used to its maximum as any programming language should. In general, the closer to the "source" you push the logic - the better the performance.

In this particular case - SQL will not be useful in all probability. But in general, if you want to paginate through things on the web - you are best served by USING SQL to get page "N".

This site does, many efficient sites do.

SQL is not storage. SQL is a programming language. It can and should be used to its maximum as any programming language should. In general, the closer to the "source" you push the logic - the better the performance.

In this particular case - SQL will not be useful in all probability. But in general, if you want to paginate through things on the web - you are best served by USING SQL to get page "N".

This site does, many efficient sites do.

Barbara Boehmer and I provided two different "SQL" solutions in OraFAQ.

See the posts at and following the url:

http://www.orafaq.com/forum/mv/msg/163723/484309/102589/#msg_484309

Regards

Michel

You are (obviously and as usual) correct that SQL is programming language. With respect to the idea that the UI layer should be handling the logic, I have been doing primarily desktop applications and was assuming the entire data set would be populated in a paginated grid.

Given the desired output, wouldn't some simple analytics do the trick?

And, yes, I am truly sorry for continuing the "z" naming convention.

Given the desired output, wouldn't some simple analytics do the trick?

SELECT colz, DENSE_RANK () OVER (ORDER BY letterz) AS pagez FROM (SELECT colz, SUBSTR (colz, 1, 1) letterz FROM fieldz)

And, yes, I am truly sorry for continuing the "z" naming convention.

... the entire data set would be populated in a paginated grid. ...

welcome to the internet where that should never be happening.

your solution would not get their desired results in general. They wanted page numbers assigned to things - not just a simple rank. If 300 things all had the letter 'A', you would assign 1 to them all - they wanted 10 of them on page one, 10 on page 2 and so on.

welcome to the internet where that should never be happening.

your solution would not get their desired results in general. They wanted page numbers assigned to things - not just a simple rank. If 300 things all had the letter 'A', you would assign 1 to them all - they wanted 10 of them on page one, 10 on page 2 and so on.