* Nikolas
> The problem with LIMIT is that this table is not the same always, the
> ordering depends on data that changes.
>
> I am ordering a table that contains webpages by the number of times they
> have been visited. Then, for a specific page of them, I want to say in
> which position of popularity it is.
>
> I think, in the meantime, I found the way. I will make a COUNT of all
> pages, then I will make a COUNT of the pages that have been visited
> less times than the one I want and then I will add 1 to that. Am I in
> the right direction. Thanks for your interest.
This can be done with a single select:
mysql> create table pages (pageno int,visits int);
Query OK, 0 rows affected (0.05 sec)
mysql> insert into pages values (1,3),(2,4),(3,1),(4,2),(5,1),(6,3),(7,4);
Query OK, 7 rows affected (0.01 sec)
Records: 7 Duplicates: 0 Warnings: 0
mysql> select * from pages order by visits desc;
+--------+--------+
| pageno | visits |
+--------+--------+
| 2 | 4 |
| 7 | 4 |
| 1 | 3 |
| 6 | 3 |
| 4 | 2 |
| 3 | 1 |
| 5 | 1 |
+--------+--------+
7 rows in set (0.03 sec)
mysql> select count(*)+1 as rank from pages,pages p2
-> where p2.pageno=1 and pages.visits>p2.visits;
+------+
| rank |
+------+
| 3 |
+------+
1 row in set (0.00 sec)
mysql> select count(*)+1 as rank from pages,pages p2
-> where p2.pageno=4 and pages.visits>p2.visits;
+------+
| rank |
+------+
| 5 |
+------+
1 row in set (0.00 sec)
--
Roger
query
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
