sql, database query

could anyone te开发者_JAVA百科ll me how to select particular column if i dont know the schema of table .Like if i want only the values in the second column of database table without knowing the column name. Is there a way to do that?

You should almost NEVER rely on column number in a table in any of your code (even though you CAN theoretically do so technically for certain database libraries in certain languages).

There are many reasons, one of the most important is that someone can always ALTER the table and insert a column in the beginning / middle, breaking your code completely.

A second reason is that column positions - even if you assume that the table never changes - make for absolutely UNREADABLE and therefore impossible to maintain code. Will you remember that column 13 was "last_name_3" 2 years from now?

Although I'd never recommend doing what you propose, assuming your RDBMS has implemented the SQL-92 INFORMATION_SCHEMA standard, you could get the column name and use it to build dynamic SQL.

SELECT c.COLUMN_NAME 
    FROM INFORMATION_SCHEMA.COLUMNS c 
    WHERE c.TABLE_NAME = 'YourTable' 
        AND c.ORDINAL_POSITION = 2

Not sure what you wanted to do. And I sincerely expect you don't do what you're doing. But if you jsut wanted to know how to play with technicality. Here is an example to do it. There may be better ways.

mysql> desc test;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int(11)      | YES  |     | NULL    |       |
| name  | varchar(100) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> select * from test;
+------+-------+
| id   | name  |
+------+-------+
|    1 | name1 |
|    2 | name2 |
|    3 | name3 |
|    4 | name4 |
+------+-------+
4 rows in set (0.00 sec)

mysql> select @colid:=column_name from information_schema.columns where table_schema='test' and table_name='test' and ordinal_position=2;
+---------------------+
| @colid:=column_name |
+---------------------+
| name                |
+---------------------+
1 row in set (0.01 sec)

mysql> set @sqlstr:=concat('select ', @colid, ' from test');
Query OK, 0 rows affected (0.00 sec)

mysql> prepare sttmt from @sqlstr;
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> execute sttmt;
+-------+
| name  |
+-------+
| name1 |
| name2 |
| name3 |
| name4 |
+-------+
4 rows in set (0.00 sec)

refer the last example here http://dev.mysql.com/doc/refman/5.0/en/user-variables.html

By the way, Google gives first link as perfect solution. Select statement with column number instead of column names

Can you do a select * from mytable to figure out what the column names are?

You could also try select * from information_schema.columns where table_name = 'mytable' if your database supports an information_schema view. There are other ways to figure out the names of columns. I would suggest just trying to figure out the name over any other possible workaround.

This could be done with SQL Server/.NET like this:

    SqlCommand command =
        new SqlCommand("SELECT * FROM TABLE", connection);
    connection.Open();

    SqlDataReader reader = command.ExecuteReader();

    // Call Read before accessing data.
    while (reader.Read())
    {
        string secondVal = reader[1].ToString();
    }

I would take a hard look at why you need to do this in the first place though. Seems fishy to me...