how to number the datas from mysql

Question

Hi This is a doubt on mysql select query let me axplain my doubt with a simple example consider this is my query SELECT dbCountry from tableCountry

tableCountry has fields dbCuntryId, dbCountry and dbState I have the result as

dbCountry 
india 
america 
england
kenya 
pakisthan

I need the result as

1  india 
2  america 
3  england
4  kenya 
5  pakisthan

the numbers 12345 must be generated with the increase in data and it is not an autoincrement id. How can i get it is it something like loop

Answer 1

The following should do what you need. It uses a variable that is incremented and returned for each row:

SELECT
  @rownum:=@rownum+1 number,
  c.dbCountry
FROM
  tableCountry c,
  (SELECT @rownum:=0) r

If you want the result to always be in the same order you'll need to add an order by constraint to the query, for example, ORDER BY c.dbCountry to order by the country name.

Answer 2

You can try this:

SELECT dbCountry,
(SELECT COUNT(*) FROM tableCountry t2 WHERE t2.dbCountry <= t1.dbCountry) 
AS RowNum
FROM tableCountry t1
ORDER BY dbCountry