--- In IBDI@y..., Ed Malloy <edm@B...> wrote:

> Hi All,
>
> I think Ann's example might be a little ragged... in that are we

sure

> that only one showing would meet all the == criteria? But I dont

ft's

> criticism is correct. Wouldn't Ann's query simply exclude movies

that

> started before the last displayed entry.

Suppose one is looking for a list of screenings which fulfill several
criteria including screening time between 7pm and midnight, and there
are 100 screenings that fullfill the criteria, including 60 that
begin between 7pm and 10:30. The initial screen will show 20 of the
100 according to the orderby clauses. Suppose the last item on the
first page is an 11 pm screening of the first movie on the list. The
second query will exclude all screening of all the rest of the movies
that begin before 11 pm.

> AND, I am totally lost on this idea of ROWNUM. How can we have

ROWNUM

> in a relational database? We are not guaranteed any ordering of the

raw

> data. Am I missing something.....

Yes, please read all the posts in this and a related thread. ROWNUM
is the index within a result set, not in a table. One of its uses
with Oracle is for selecting subsets of the result set.

> However, I don't see any problem... Wouldn't we just read All of the
> data into a data structure in memory and assign row numbers to this
> stucture and supply the data sets to the various pages from here.

Is it

> critical that these data are not "live?" Couldn't that be solved by
> alowing the user to update the data at any time? what's the problem.

No problem at all for some tasks. If you read the messages from Paulo
to which Ann was responding you will see that for some types of
application you will need an infinite number of arrays with different
sorting orders as indexes to the structure you are proposing, in
other words, the programmer will have to write a mini relational
database.

> On the other issues of an ISP database. They seem valid, except

maybe I

> am missing something also. Why would I want an ISP to run the

database,

> instead of myself. Why would I want to "share" a database engine??
> Indeed, do I have to have the database on the same computer? I

don't.

>

You may not need or want an ISP to run your database, neither do I.
Others do.


Fred Toussi

> ft@c... wrote:
> >
> > Oops!
> >
> > This type of query won't work. Suppose the last movie in the first
> > list is a late night screening, the next query will exclude almost
> > all the movies that the full dataset of the first query would

return.

> >
> > In general, in a dataset sorted on multiple keys, you need a

unique

> > key sorted according to the combined aggregate sorting order in

order

> > to be able to select consecutive parts. ROWNUM is such a key with

the

> > added property of being a counter.
> >
> > Fred Toussi
> >
> > --- In IBDI@y..., "Ann W. Harrison" <aharrison@i...> wrote:
> > > At 04:48 PM 6/5/2001 +0200, Paulo Gaspar wrote:
> > >
> >
> > > My inclination would be to construct an initial query like this:
> > >
> > > select <whatever> from movies m
> > > join schedules s on s.movie = m.movie
> > > join cinemas c on c.cinema = s.cinema
> > > join towns t on t.town = c.town
> > > where 50 <= distance (mycity, city)
> > > and s.date >= CURRENT_DATE and s.date < CURRENT_DATE

+ 7

> > > order by m.movie, t.town, c.cinema, s.room, s.date, s.time
> > >
> > > I'd get the first 20, noticing the values of the last row

retrieved.

> > > When the user hits next, I'd issue this query:
> > >
> > > select <whatever> from movies m
> > > join schedules s on s.movie = m.movie
> > > join cinemas c on c.cinema = s.cinema
> > > join towns t on t.town = c.town
> > > where 50 <= distance (mycity, city)
> > > and s.date >= CURRENT_DATE and s.date < CURRENT_DATE

+ 7

> > > and m.movie >= :saved_movie
> > > and t.town >= :saved_town
> > > and c.cinema >= :saved_cinema
> > > and s.room >= :saved_room
> > > and s.date >= :saved_date
> > > and s.time >= :saved_time
> > > order by m.movie, t.town, c.cinema, s.room, s.date, s.time
> > >
> > > I'd get 21 of these, throw out the first one as a duplicate, and
> > > return those values. Prior is left as an exercise for the

student.

> > >
> > > If I offered the user the ability to click on an arbitrary page
> > > rather than just go through hitting next and prior, I'd retrieve
> > > enough data to fill the range of pages I offered (as google
> > > appears to do) and get more if the user showed signs of moving
> > > out of that range.
> > >
> > > Actually, and in fact, I'd write an intermediate application
> > > designed to cache significant quantities of data. This is a
> > > place where cheap memory is a real win.
> >
> > Community email addresses:
> > Post message: IBDI@y...
> > Subscribe: IBDI-subscribe@y...
> > Unsubscribe: IBDI-unsubscribe@y...
> > List owner: IBDI-owner@y...
> >
> > Shortcut URL to this page:
> > http://www.yahoogroups.com/community/IBDI
> >
> > Your use of Yahoo! Groups is subject to

http://docs.yahoo.com/info/terms/