SQL - How to find index

Question

Hello,

I am using MySQL 5.0.

I have table with following structure and data.

CREATE TABLE `test` (
  `text1` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `test` (`text1`) VALUES ('ABC'),
('PQR'),
('XYZ'),
('LMN');

I want to display following output.
Index   Text
1          ABC
2          LMN
3          PQR
4          XYZ

In table structure, i don't want to create any new coloum.

For that how can i write SQL Query or any store procedure?

Thanks in advance.

Answer 1

This will not answer your question, but you should really reconsider what you are asking for.

There are no index. You can print a number for each row in your application code, but that number would be meaningless. Without an actual index field in your table, you can't be sure what each number means. For example, if two users remove row "2" simultaneously, both LMN and PQR may be deleted. This is unexpected and dangerous.

You are also not guaranteed to get the rows in the same order each time you do a query if you lack an ORDER BY clause.

Answer 2

 select @rownum:=@rownum+1 Id, text1
 from test, (SELECT @rownum:=0) r 
 order by text

Answer 3

SET @row=0;   
SELECT @row := @row + 1 AS Index, text1 as Text FROM test;