The ranking in ORDER BY

[midnite]

for better descripe my problem, here comes an example:

mysql> CREATE TABLE ranking (rank int primary key, uni varchar(20), area varchar(20));Query OK, 0 rows affected (0.01 sec)mysql> INSERT INTO ranking VALUES (1,'Oxfrod','UK'), (2,'CamBridge','UK'), (3,'UST','HK'), (4,'Impreial','UK'), (5,'HKU','HK'), (6,'MiB','UK');Query OK, 6 rows affected (0.00 sec)Records: 6  Duplicates: 0  Warnings: 0mysql> SELECT * FROM ranking;+------+-----------+------+| rank | uni	   | area |+------+-----------+------+|	1 | Oxfrod	| UK   ||	2 | CamBridge | UK   ||	3 | UST	   | HK   ||	4 | Impreial  | UK   ||	5 | HKU	   | HK   ||	6 | MiB	   | UK   |+------+-----------+------+6 rows in set (0.00 sec)mysql> SELECT uni FROM ranking WHERE area = 'UK' ORDER BY rank;+-----------+| uni	   |+-----------+| Oxfrod	|| CamBridge || Impreial  || MiB	   |+-----------+4 rows in set (0.01 sec)

rank is the world rank of the uni. Now i want to find the rank of uni = Impreial in UK. If finding the world rank of Impreial, it is easy:

mysql> SELECT uni, rank FROM ranking WHERE uni = 'Impreial';+----------+------+| uni	  | rank |+----------+------+| Impreial |	4 |+----------+------+1 row in set (0.00 sec)

But now i want is to produce a table like this:

+-----------+-----------+| uni	   | area_rank |+-----------+-----------+| Oxfrod	|		 1 || CamBridge |		 2 || Impreial  |		 3 || MiB	   |		 4 |+-----------+-----------+

or more specific:

+-----------+-----------+| uni	   | area_rank |+-----------+-----------+| Impreial  |		 3 |+-----------+-----------+

Hope i have make it clear enough. Thanks in advance for any kindly help :]

[Yahweh]

Use this:

SELECT uni, rank as area_rank FROM ranking WHERE uni = 'Impreial'

The "as area_rank" is an alias. It just renames your column to something else.You can also use this to display more rows:

SELECT uni, rank as area_rank FROM ranking ORDER BY area_rank ASC

[midnite]

i am sorry if i didnt make it clear enough...i want it ranks in UK only.

mysql> SELECT uni, rank AS area_rank FROM ranking;+-----------+-----------+| uni	   | area_rank |+-----------+-----------+| Oxfrod	|		 1 || CamBridge |		 2 || UST	   |		 3 || Impreial  |		 4 || HKU	   |		 5 || MiB	   |		 6 |+-----------+-----------+6 rows in set (0.01 sec)mysql> SELECT uni, rank AS area_rank FROM ranking WHERE uni = 'Impreial';+----------+-----------+| uni	  | area_rank |+----------+-----------+| Impreial |		 4 |+----------+-----------+1 row in set (0.00 sec)

it gives me 4 instead of 3.

[pulpfiction]

Try this,...

SELECT   uni, (SELECT COUNT(*) FROM ranking r2 WHERE r2.rank <= r1.rank AND r2.area= 'UK') AS newrankFROM		 ranking r1WHERE	 (r1.area = 'UK')

[midnite]

pulpfiction, you are really genius!!i not only get your answer, but also learnt from it!!

mysql> SELECT * FROM ranking;+------+-----------+------+| rank | uni	   | area |+------+-----------+------+|	1 | Oxfrod	| UK   ||	2 | CamBridge | UK   ||	3 | UST	   | HK   ||	4 | Impreial  | UK   ||	5 | HKU	   | HK   ||	6 | MiB	   | UK   ||	7 | Impreial  | HK   |+------+-----------+------+7 rows in set (0.00 sec)mysql> SELECT uni, area, (SELECT COUNT(*) FROM ranking r2 WHERE r2.rank <= r1.rank && r1.area = r2.area) AS new_rank FROM ranking r1 WHERE r1.uni = 'Impreial';+----------+------+----------+| uni	  | area | new_rank |+----------+------+----------+| Impreial | UK   |		3 || Impreial | HK   |		3 |+----------+------+----------+2 rows in set (0.00 sec)

see? i can do it if i want to list the local ranks of a particular university :]* Note that i am intended to spell them wrongly or using short forms that they are NOT referring to any real universities