Mysql Select Specific Column with N Column

Question

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'

make sure there is no typo - too many ' quote mark near ' FROM 'tb_data'. if error still persist, let us know. also why do you have SELECT Name while you dont have any reference to that Name column, remove it maybe? — Boyke Ferdinandes
– Boyke Ferdinandes, Commented Jul 28, 2020 at 4:08
@BoykeFerdinandes i want to select 10 last column with column(Name) so that makes 11 column selected — Kanien Notiros
– Kanien Notiros, Commented Jul 28, 2020 at 4:12
what value do you expect to see from SELECT Name ? last 10 columns should be covered by the link you mentioned — Boyke Ferdinandes
– Boyke Ferdinandes, Commented Jul 28, 2020 at 4:14
oh Name is from tb_data ? my bad didnt get you in the beginning, I'll post an answer soon — Boyke Ferdinandes
– Boyke Ferdinandes, Commented Jul 28, 2020 at 4:21

Boyke Ferdinandes · Accepted Answer · 2020-07-28 04:24:59Z

1

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;

answered Jul 28, 2020 at 4:24

Boyke Ferdinandes

4112 silver badges7 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Mysql Select Specific Column with N Column

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related