How to select only first column dynamically on mysql

Posted on

Question :

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;

Answer :

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

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.

Leave a Reply

Your email address will not be published. Required fields are marked *