CODEWITHSUNDEEP
mysql
Kanien Notiros
Kanien Notiros

Reputation: 27

Mysql Select Specific Column with N Column

Let's say i have 'db_institute' and 'tb_data' which have 39 Column

While This Answers Selecting from the last 10 column, i want to add extra column in the query

the result would be like this when i prepared statement it

SELECT Name,lastcolumn1,...,lastcolumn10 from tb_data

And here's my failed attempt

    SELECT Name,
      CONCAT('SELECT ', 
             GROUP_CONCAT(COLUMN_NAME), 
             ' FROM 'tb_data') 
    FROM 
      (SELECT 
        COLUMN_NAME, 
        ORDINAL_POSITION 
      FROM 
        INFORMATION_SCHEMA.COLUMNS 
      WHERE 
        TABLE_SCHEMA='db_institute' 
        AND 
        TABLE_NAME='tb_data' 
      ORDER BY 
        ORDINAL_POSITION DESC LIMIT 10) AS ord_desc 
    ORDER BY 
      ord_desc.ORDINAL_POSITION
  into @sql

Which result error

Unknown column 'Name' in 'field list'

Upvotes: 0

Views: 38

Answers (1)

Boyke Ferdinandes
Boyke Ferdinandes

Reputation: 411

your reference is already correct. try this

SELECT 
  CONCAT('SELECT Franchisee, ', GROUP_CONCAT(COLUMN_NAME), ' FROM product_staging') # change product_staging to your table name and Franchisee to Name
FROM 
  (SELECT 
    COLUMN_NAME, 
    ORDINAL_POSITION 
  FROM 
    INFORMATION_SCHEMA.COLUMNS 
  WHERE 
    TABLE_SCHEMA='myob' # change it to your schema name
    AND 
    TABLE_NAME='product_staging' # change it to your table name
  ORDER BY 
    ORDINAL_POSITION DESC LIMIT 10) AS ord_desc 
ORDER BY 
  ord_desc.ORDINAL_POSITION
INTO @qry;
PREPARE stmt1 FROM @qry;
EXECUTE stmt1;

Upvotes: 1

Related Questions