I am rather new to databases and do not have much idea about
application specific sql functionality. I have to implement a ranking
system for which the details are as follows:

Each tuple in a table has an attribute whose value is modified at
frequent intervals. After a certain span of time, the tuples in the
table are to be arranged in the order of the attribute. However after
sorting the tuples, a rank must be assigned to each tuple depending
upon the position in the sorted table. This rank is stored in a
separate attribute in the same table.

Can someone guide me on implementing this model in a MySql database.
Also if it is easier, in the Oracle database.

On Aug 30, 2:10 pm, Sameer <sameervijay... (AT) gmail (DOT) com> wrote:
First off, TABLES ARE NOT SORTED. Table repesent Sets of data. Sets
are UNORDERED collections.

Separately from that issue, such ranking is really an attribute of a
reporting application. I think it is a poor idea to store the rank in
the table, especially as it depends on
lets assume this interval is one minute. Then your second requirement:
Lets assume this "span of time" is 5minutes. That means for the minute
after the rank is assigned it is correct. Then the attribute is
updated on one or more rows and likely at least two of the rows have
the wrong rank. So for the other 4minutes the ranking is wrong. That
means 80% of the time your table has the wrong ranking!

So two things:
1. consider creating a view, which computes the rank when needed (ie
when queried)
2. in Oracle the view can use the RANK operation (see Analytic
Functions). I have no idea if MySQL supports this feature.

HTH,
ed

Suppose your data looks like this initially:

T_Data
Name Points Rank
john 100
mary 50
bob 500
sue 5

You could use cursor/code similar to below to update Rank:

Dim x
x = 0
Set rs = db.OpenRecordset
("SELECT Rank FROM T_Data ORDER BY Points DESC")
rs.MoveFirst
Do Until rs.EOF
x = x + 1
rs("Rank") = x
rs.MoveNext
Loop

After calling above function, your table would look like:

T_Data
Name Points Rank
john 100 2
mary 50 3
bob 500 1
sue 5 4

"Ed Prochak" <edprochak (AT) gmail (DOT) com> wrote

What Ed said. Simplified, this means that it's better under most
circumstances, to order (sort) data at the time of retrieval, and not at the
time of insertion. The "order by" clause of a "select" does exactly this.

However, many times it's advantageous to bear the COST of sorting at
insertion time, because the delay is just too long at retrieval time.
Appropriate index design will accomplish this goal, for the most part. If
there is an index on the table that maintains the data in the order it will
be retrieved, then updating this index will usually incur some cost at
insertion time, but retrieval in the same order as the index will generally
be pretty fast.

It gets more complicated than this, but I wanted to give you an overview.