Hi all,

does someone of you know how to find a special
row in a table, not using a WHERE clause?
To make it more clear: What do I have to enter if I
want to know the contents of row number six in table <abc>?

Any tricks???

Thank you very much in advance - Alfred


--
Posted via Mailgate.ORG Server - http://www.Mailgate.ORG

Alfred Zentek wrote:

The key is to define what you mean by 'row number 6'. Is this the
chronologically sixth inserted row? Is it the sixth row as defined
by a unique key? Is it just the sixth row you currently see when you
do a 'select(*)'? Does this table have any indexes? Any of them clustered?
Depending on your answers, there may be one trick or another.

Joe Weinstein at BEA

From your answer I learned a lot for the questions in the future,
thank you so far.

In fact, this table has 5 Indexes, one of them is a clustered one.
But, your guess was right I'm at the moment only interested to fetch
for example the sixth row of the table if I do a select (*).
Background information: Currently programming a data generator which
will just generate data on a random basis but relies on data in
different
tables.

Thank's in advance - Alfred


--
Posted via Mailgate.ORG Server - http://www.Mailgate.ORG

Alfred Zentek wrote:

Ok. To stick to what you can expect from every DBMS, a plain
'select * from foo' is not guaranteed to return the rows in
the same order as it did the last time. Practically though,
a DBMS usually does return them in the same order. If you want
random data, you should consider the rand() function, but
to get what is curently the sixth row according to some order,
such as by your clustrered index, I would do a query like:

select * from mytable A
where 5 = (select count(*) from mytable AA where AA.myUniqueKeyCol < A.myUniqueKeyVal)

In other words, for every row in the table, we do a subquery on the same table
to cound how many rows come before the current row in the main query, and
we only return the row that has 5 rows before it in the table.

This isn't going to be fast, and for big tables it'll be a pain.

On the whole, I'd recommend another way of generating random data.
I'd consider caching the table data in files or the client, and
choosing a random index into an array. Just refresh the cached data
when needed.
Joe Weinstein at BEA

Thank for all the tips I got - works :-)

KR Alfred


--
Posted via Mailgate.ORG Server - http://www.Mailgate.ORG