SELECT COUNT($col)
    FROM $table
   WHERE $col <= $this_row_col_value
[download]

Or perhaps I misunderstand what you mean by "rank", if so, please explain.

It depends on what your dbms supports, but look at the ORDER BY and LIMIT clauses.

I know those clauses but I can't see how they would help me get the numeric rank of a given row or value efficiently.

SELECT COUNT(*) FROM table WHERE numeric_column < 
  (SELECT numeric_column FROM table WHERE your_criteria)
[download]

In my version of mysql, subqueries aren't supported, so I actually have to save the subselect results into a variable, then do the second select, but EXPLAIN says this similar query is reasonably well-optimized:

mysql> explain select count(*) FROM faar_homes WHERE price < 80000 \G
*************************** 1. row ***************************
        table: faar_homes
         type: range
possible_keys: price
          key: price
      key_len: 5
          ref: NULL
         rows: 25
        Extra: where used; Using index
[download]

"SELECT COUNT(*) FROM table WHERE numeric_column < 
  (SELECT numeric_column FROM table WHERE your_criteria)"
[download]

I believe you knew, but just to make it complete. The criteria has to make sure that only one row is returned from the subquery. Otherwise you would get error like 'too many rows returned'.

Having is kind of like where, but only works with group by. It allows you to pick up groups that meets the criterias defined in having.

But having does not give you order, nor does group by. Although most of the DBMS systems actually do sorting for group by, it is not required by SQL standard, so unless it is clearly stated in document, don't assume it does the ordering.

For example, oracle does not always sort group by, but sometime it sorts. You will see this, when you do explain. Oracle will tell you whether it is 'sorted group by' or just a 'group by'. But this is not controled by you, so in Oracle, you still have to order by after group by, to ensure the ordering is really there. This does not degrade the performance, as Oracle will skip the order by silently, if the group by is 'sorted'.

Well I think in the worst case I could confine the problem to an O(log N) binary search via the LIMIT clause, but I'm hoping for an O(1) solution.

select what, ever, rownum RANK from something where rownum between 123
+ and 321;
[download]

Actually rownum doesn't work quite like that.

Tom has a good explanation of what it does. It could be useful in the original poster's case though.

/J

Don't forget the LIMIT part. It makes the search more efficient. Note also that idn should be a primary key auto-increment, so it returns the row number assuming you don't delete any rows (better to flag rows for delete and then replace with fresh data later on).

On the other hand, if you're just doing this from a text file, you can set up a secondary file with all the index values and line numbers in order by index, and then perform a binary search to find the line number you want. Then the line number can be used to access the actual data in the other file.

Just to clarify something here. The 'limit' clause has no effect on the efficiency of the search. It affects the overall efficiency of the query because it prevents you from accidentally requesting more rows than you can handle.

In the case where there is a unique index on the field (which is essentially what a PK-identity is anyway) the limit is entirely superlfuous and probably actually slows things down just marginally as its more to be marshalled accross the interprocess or intermachine barrier. Also to the best of my knowledge 'limit' is not standard SQL but rather a MySQL extension.

Anyway. ;-)



---
demerphq


First they ignore you, then they laugh at you, then they fight you, then you win.
-- Gandhi

Flux8

It is a MySQL extension, but an damn useful one and it's one of the things I consider when determining whether to use MySQL or Oracle for a given project.

Oracle does have something similar, but it's harder to do.

Being right, does not endow the right to be rude; politeness costs nothing.
Being unknowing, is not the same as being stupid.
Expressing a contrary opinion, whether to the individual or the group, is more often a sign of deeper thought than of cantankerous belligerence.
Do not mistake your goals as the only goals; your opinion as the only opinion; your confidence as correctness. Saying you know better is not the same as explaining you know better.

Here is a way to do it. SQL is a lot like Perl in the TMTOWTDI. The code joins the table on itself which is usually not a very good performer when you have millions of rows. The nice thing about this example is that it's SQL ANSI 92 standard so it should run on all compliant dBs and it gives you the result you want.

CREATE TABLE Abc(letter char(1), position int)
INSERT INTO Abc (letter, position) VALUES ('a', 1)
INSERT INTO Abc (letter, position) VALUES ('b', 2)
INSERT INTO Abc (letter, position) VALUES ('d', 4)
INSERT INTO Abc (letter, position) VALUES ('j', 10)
INSERT INTO Abc (letter, position) VALUES ('z', 26)

SELECT COUNT(*) AS rank, a1.letter
FROM Abc a1
JOIN Abc a2 ON a1.letter > a2.letter
GROUP BY a1.letter
[download]

Oops, make that join

JOIN Abc a2 ON a1.letter >= a2.letter
[download]

otherwise 'a' gets lost. Sorry about that.