Select last N# of columns of a table from MySQL

Question

I have tables with more than 35 columns, the first 20 columns are fixed and the column number is different in each table. I need to select the last 10 columns for example from a table, how can I achieve that? Just like this query returns the top 20 records

select * from table1 limit 10;

I want to do the same with columns I mean return the column names in a query and then use those names in another query, something like:

SELECT (SELECT column_name FROM INFORMATION_SCHEMA.Columns WHERE TABLE_NAME = 'table1' ) FROM table1;

Answer 1

You may achieve that with prepared statements. Your query will look like:

SELECT 
  CONCAT('SELECT ', 
         GROUP_CONCAT(COLUMN_NAME), 
         ' FROM test') 
FROM 
  (SELECT 
    COLUMN_NAME, 
    ORDINAL_POSITION 
  FROM 
    INFORMATION_SCHEMA.COLUMNS 
  WHERE 
    TABLE_SCHEMA='test' 
    AND 
    TABLE_NAME='test' 
  ORDER BY 
    ORDINAL_POSITION DESC LIMIT 10) AS ord_desc 
ORDER BY 
  ord_desc.ORDINAL_POSITION

-this will create an SQL with content like:

SELECT date,title FROM test 

(in sample above I had 2 column within selection, that can be adjusted in this part: ORDER BY ORDINAL_POSITION DESC LIMIT 10)

So all you need to do is to prepare this statement. In my case:

SQL:

mysql> set @sql=(SELECT CONCAT('SELECT ', GROUP_CONCAT(COLUMN_NAME), ' FROM test') FROM (SELECT COLUMN_NAME, ORDINAL_POSITION FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA='test' AND TABLE_NAME='test' ORDER BY ORDINAL_POSITION DESC LIMIT 2) AS ord_desc ORDER BY ord_desc.ORDINAL_POSITION);
Query OK, 0 rows affected (0.02 sec)

Prepare:

mysql> prepare stmt from @sql;
Query OK, 0 rows affected (0.00 sec)

Result:

mysql> execute stmt;
+------------+--------------+
| date       | title        |
+------------+--------------+
| 2014-02-04 | my event 001 |
| 2014-02-04 | my event 002 |
| 2014-02-05 | my event 003 |
| 2014-02-05 | my event 004 |
| 2014-02-05 | my event 005 |
| 2014-02-07 | my event 006 |
| 2014-02-07 | my event 007 |
+------------+--------------+
7 rows in set (0.00 sec)