MYSQL: How to refer to the 1st column

Question

SELECT first_name FROM user WHERE FIRST_COLUMN = '10'

What I need to know is how to reference "FIRST_COLUMN" in MYSQL syntax. The first column can be any name and so I need to make it flexible as long it should get the 1st column of any table. thanks

Answer 1

 SELECT COLUMN_NAME FROM information_schema.COLUMNS 
    WHERE TABLE_NAME = 'tablename' 
    AND ORDINAL_POSITION =1

Answer 2

The relational model does not care one tiny little bit what order columns are in within a table, nor (without an ordering clause) what order rows are returned.

Your requirement makes little sense. What if the first column were a varchar or a date?

The whole point of having named columns is that you reference them by name.

Now DBMS' often contain metadata in system tables, like DB2's sysibm.systables and sysibm.syscolumns, but you need to extract not just the names but all the other metadata as well (column type, size, nullable, and so on) in order to use them properly. We'd probably understand better what you were after if you told us the reason behind doing this.

Answer 3

To get the first row of your table you can use the LIMIT sql-statement. Just add

LIMIT 1, 1 

E.g.

SELECT first_name FROM user LIMIT 1,1

This sql returns the first row.

Answer 4

The short answer is : you can't. (in a portable way, there are 'tricks' to look up the name of the first column and similar workarounds)

Many projects use as convention to name the first column ID and use that as the primary key.

By the query it looks like you are uing it as a primary key.

I recommend reading an introduction to relational databases as this is a rather strange request in the context of a relational database.

Answer 5

You can access columns by an index.