SQL My wrote: > Hi! dear all, > I encountered a problem with designing SQL statement. For example, I > want to query population information from a "city" table, and the > records returned must be ordered according to the population of the > city. Like this: > +---------------------------+---------------------------------+---------+ > |capital | country | Population | > +---------------------------+---------------------------------+------------+ > | Seoul | South Korea | 9981619 | > |Jakarta | Indonesia | 9604900 | > |Ciudad de M騙ico | Mexico | 8591309 | > |Moscow | Russian Federation | 8389200 | > |Tokyo | Japan | 7980230 | > |Peking | China | 7472000 | > |London | United Kingdom | 7285000 | > |Cairo | Egypt | 6789479 | > > This is a very simple SQL statement, but I want to add sequence number > to each city, for example Seoul is number 1, Jakarta is number 2 and > so on. Namely like this: > +-------+---------------------------+---------------------------------+---------+ > | |capital | country | > Population | > +-------+---------------------------+---------------------------------+------------+ > | 1 | Seoul | South Korea | 9981619 | > | 2 |Jakarta | Indonesia | 9604900 | > | 3 |Ciudad de M騙ico | Mexico | 8591309 | > | 4 |Moscow | Russian Federation | 8389200 | > | 5 |Tokyo | Japan | 7980230 | > | 6 |Peking | China | 7472000 | > | 7 |London | United Kingdom | 7285000 | > | 8 |Cairo | Egypt | 6789479 > | > > Then, how do I get such a result? > > Best wishes > Hongwei Liu > 2005/06/03 With a user variable <http://dev.mysql.com/doc/mysql/en/variables.html>. Something like this: SET @r = 0; SELECT @r:[EMAIL PROTECTED] AS rank, capital, country, Population FROM city ORDER BY Population DESC; Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
