Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have to select only first column. Because of i dont want to select all columns like SELECT * FROM table. But my first columns may vary like following:

id
article_id
user_id
product_id
page_id
etc..

Is there any way always getting only first column. For example:

SELECT 0 FROM table;

or

SELECT FIRST_COLUMN FROM table;
share|improve this question
add comment

2 Answers 2

It's not clear why you need this but technically you can do it with dynamic SQL like this

SET @sql = NULL;
SELECT column_name 
  INTO @sql
  FROM INFORMATION_SCHEMA.COLUMNS
 WHERE table_schema = SCHEMA()
   AND table_name = 'users'
   AND ordinal_position = 1;

SET @sql = CONCAT('SELECT `', @sql, '` FROM users');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Here is SQLFiddle demo


You can wrap it up into a stored procedure

DELIMITER $$
CREATE PROCEDURE select_first_column(IN _tname VARCHAR(64))
BEGIN
  SET @sql = NULL;
  SELECT column_name 
    INTO @sql
    FROM INFORMATION_SCHEMA.COLUMNS
   WHERE table_schema = SCHEMA()
     AND table_name = _tname
     AND ordinal_position = 1;

  SET @sql = CONCAT('SELECT `', @sql, '` FROM `', _tname, '`');

  PREPARE stmt FROM @sql;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
END$$
DELIMITER ;

Sample usage:

CALL select_first_column('users');
CALL select_first_column('orders');

Sample output:

| USER_ID |
|---------|
|       1 |
|       2 |

| ORDER_ID |
|----------|
|      101 |
|      202 |

Here is SQLFiddle demo

share|improve this answer
add comment

In PostgreSQL you can do it using column aliasing:

postgres=# CREATE TABLE product (product_id INTEGER, name TEXT);
CREATE TABLE

postgres=# INSERT INTO product (product_id, name) VALUES (1, 'product 1'), (2, 'product 2');
INSERT 0 2

postgres=# SELECT p.id FROM product p (id);
 id 
----
  1
  2
(2 rows)

Don't know if it works the same way in MySQL, though.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.