select first N columns of MySQL table

Question

As it is possible to select top N rows from table, is there any way to select first N columns from MySQL database tables?
Thanks for your replies and maybe some parts of code in PHP.

While there may be a valid reason for doing this, the implication is that there's something wrong with your storage model. — Strawberry, Jun 23 '13 at 10:00
@strawbery there is no problem with the storage model but i have a valid reason for lots of different query i need. — parsaeed, Jun 23 '13 at 11:23
A valid reason is simply browsing tables from the mysql prompt while coding (that's how I arrived at this page). — felwithe, Jun 02 '17 at 00:27
@michael back in 2013, I might have cared. I can assure you, I don't now. — Strawberry, Jun 07 '18 at 21:50

score 17 · Accepted Answer · edited Feb 10 '18 at 20:47

Please have a look at Bill Karwin's answer first. But if you know how to order your column names there could be a solution that makes use of a dynamic query.

To select all column names from a table, you can use a query like this:

SELECT `column_name` 
FROM   `information_schema`.`columns` 
WHERE  `table_schema`=DATABASE() 
       AND `table_name`='yourtablename';

(please have a look at this answer). And making use of GROUP_CONCAT:

GROUP_CONCAT(CONCAT('`', column_name, '`') ORDER BY column_name)

we can return all column names in a single row, separated by commas:

`col1`, `col2`, `col3`, ...

(I also added quotes around the name of the column, and please notice that we have to order our list of columns somehow, otherwise there are no guarantees about the order in which column names are returned).

Then we can cut the returned string, using SUBSTRING_INDEX, in order to get, for example, the first 2 column names:

SUBSTRING_INDEX(columns, ',', 2)

and our final query, that concatenates 'SELECT ', the selected columns above, and ' FROM Tab1', and inserts the resulting string into the @sql variable is this:

SELECT
  CONCAT(
    'SELECT ',
    SUBSTRING_INDEX(
      GROUP_CONCAT(CONCAT('`', column_name, '`') ORDER BY column_name),
      ',',
      2),
    ' FROM Tab1'
  )
FROM
  information_schema.columns 
WHERE
  table_schema=DATABASE() 
  AND table_name='Tab1'
INTO @sql;

It's value will be something like this:

@sql = "SELECT `col1`, `col2` FROM Tab1"

and you can then prepare your statement, and execute it:

PREPARE stmt FROM @sql;
EXECUTE stmt;

Please see fiddle here.

Nice and complete answer. thanks alot. i think if you add more information and add the table views of this fiddle to your answer and also add some useful comments on the codes lines, it become a nice wiki answer. — parsaeed, Jun 23 '13 at 11:16
@parsaeed thank you! you are right, i edited and added more informations to my answer, i hope everything is clear! tnx — fthiella, Jun 23 '13 at 11:58
Great job! it's like a nice learning class now. thanks alot fthiella. — osyan, Jun 23 '13 at 13:26
For a general case where the columns *aren't* in alphabetical order and you still want the 1st N columns: change the `ORDER BY column_name` to `ORDER BY ordinal_position`. — Annabel, Jan 18 '18 at 14:28

score 16 · Answer 2 · answered Jun 23 '13 at 08:40

16

SQL requires that you name the columns you want, or else use the * wildcard.

In relational theory, there is no concept of "first N columns" because columns have no implicit order. Of course in any concrete implementation of SQL, they must have some storage order, but the SQL language doesn't have any support for fetching columns by "position" in the table, nor is there any support for fetching sequences of columns (except for *).

answered Jun 23 '13 at 08:40

Bill Karwin

538,548
86
673
828

That said, you could of course artificially achieve this using PHP, and by naming the columns according to some ordered convention. But no, not with SQL. – Joost Jun 23 '13 at 08:42
1

Thanks Bill for your complete information, it helped me alot and i vote it up. But fthiella and Erik answer's helped me more, because of codes they shared to me and all people visiting this question later. – parsaeed Jun 23 '13 at 11:07
1

'In relational theory, there is no concept of "first N columns" because columns have no implicit order. ' Yes. This. – BClaydon Feb 06 '17 at 23:29

score 1 · Answer 3 · answered Jun 23 '13 at 09:00

1

You cannot do this directly in MySQL, you must do this server-side. In PHP this might look like this:

<?php
$mysqli->real_query("SELECT id, title, name FROM test ORDER BY id ASC");
$res = $mysqli->use_result();

$numberOfColumnsToShow = 2;

while ($row = $res->fetch_assoc()) {
    // Only select the first $numberOfColumnsToShow columns
    $rowWithSpecifiedNumberOfColumns = array_slice($row, 0, $numberOfColumnsToShow);

    // $rowWithSpecifiedNumberOfColumns only contains the first two columns (id, title)
}
?>

answered Jun 23 '13 at 09:00

Erik Schierboom

16,301
10
64
81

I should try this. i think it could be accepted as answer but let me try first. and see if a better answer arrive :) just one question. would you do this for huge database? will this make me speed problem? – parsaeed Jun 23 '13 at 09:09
1

@parsaeed Well it depends on how big your database is. The fact is that MySQL needs to return all columns, including the columns you won't be displaying. If it will be a performance problem remains to be seen, for that you should do some performance benchmarks. – Erik Schierboom Jun 23 '13 at 09:30

select first N columns of MySQL table

3 Answers3

Linked