2

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;

2 Answers 2

3

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

0
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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