MySQL Get Table Column Names in Alphabetical Order

mysql get table column names in alphabetical order

The ANSI INFORMATION_SCHEMA tables (in this case, INFORMATION_SCHEMA.COLUMNS) provide more flexibility in MySQL:

SELECT c.column_name
  FROM INFORMATION_SCHEMA.COLUMNS c
 WHERE c.table_name = 'tbl_name'
-- AND c.table_schema = 'db_name'    
ORDER BY c.column_name

mysql get result by colum name in alphabetical order

Perhaps create view with desired order

    create view test.v as select a,b,c,d,e from table;
    select * from test.v;

INFORMATION_SCHEMA.COLUMNS sorted alphabetically initially, then a few days later sorted by ORDINAL_POSITION

In SQL order is not an inherent property of a data set. The wording that appears in the SQL-92 spec is

If an <order by clause> is not specified, then the ordering of the rows ... is implementation-dependent.

You'll find that in some form in a number of places.

There's no server-wide or database-wide option to set it. You need to specify the required order in your query. Your example above becomes

SELECT * from INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = <your table name> order by ORDINAL_POSITION;

You'll need to update your queries.

Display column names in their original order?

Try this:

select column_name
from information_schema.columns 
where table_schema = 'customer_info' 
and table_name = 'customer_contact' 
order by ordinal_position
limit 3 offset 0;

See official manual here The INFORMATION_SCHEMA COLUMNS Table

SQL Listing all column names alphabetically

Yes, and no :-)

SQL itself doesn't care what order the columns come out in but, if you were to use:

select age, name, sex from ...

you'd find that they probably came out in that order (though I'm not sure SQL standards mandate this).

Now you may not want to do that but sometimes life isn't fair :-)

You also have the other possibility of using the DBMS data definition tables to dynamically construct a query. This is non-portable but most DBMS' supply these table (such as DB/2's SYSIBM.SYSCOLUMNS) and you can select the column names from there in an ordered fashion. Something like:

select column_name from sysibm.syscolumns
where owner = 'pax' and table_name = 'movies'
order by column_name;

Then you use the results of that query to construct the real query:

query1 = "select column_name from sysibm.syscolumns" +
         " where owner = 'pax' and table_name = 'movies'" +
         " order by column_name"
rs = exec(query1)
query2 = "select"
sep = " "
foreach colm in rs:
    query2 += sep + colm["column_name"]
    sep = ", "
query2 += " from movies order by rating"
rs = exec(query2)
// Now you have the rs recordset with sorted columns.

However, you really should critically examine all queries that select * - in the vast majority of cases, it's unnecessary and inefficient. And presentation of the data is something that should probably be done by the presentation layer, not the DBMS itself - the DBMS should be left to return the data in as efficient a manner as possible.

MySQL sorting table by column names

Note: The following code will alter the specified table and reorder the columns in alphabetical order

This should do the trick. It's a bit messy and lengthy, and you'll have to change the database name and table name, but for this one, the only requirement is that there is a database named "test" and that you are running these commands in it:

Let's create the tables we need:

-- CREATE TESTING TABLE IN A DATABASE NAMED "test"
DROP TABLE IF EXISTS alphabet;
CREATE TABLE alphabet (
      d varchar(10) default 'dee' not null
    , f varchar(21)
    , e tinyint
    , b int NOT NULL
    , a varchar(1)
    , c int default '3'
);

-- USE A COMMAND STORAGE TABLE
DROP TABLE IF EXISTS loadcommands;
CREATE TABLE loadcommands (
      id INT NOT NULL AUTO_INCREMENT
    , sqlcmd VARCHAR(1000)
    , PRIMARY KEY (id)
);

Now let's create the two stored procedures required for this to work:

Separating them since one will be responsible for loading the commands, and including a cursor to immediately work with it isn't plausible (at least for me and my mysql version):

-- PROCEDURE TO LOAD COMMANDS FOR REORDERING
DELIMITER //
CREATE PROCEDURE reorder_loadcommands ()
BEGIN
    DECLARE limitoffset INT;
    SET @rank = 0;
    SET @rankmain = 0;
    SET @rankalter = 0;
    SELECT COUNT(column_name) INTO limitoffset 
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE table_schema = 'test'
        AND table_name = 'alphabet';

    INSERT INTO loadcommands (sqlcmd)
    SELECT CONCAT(t1.cmd, t2.position) AS commander FROM (
        SELECT @rankalter:=@rankalter+1 AS rankalter, CONCAT('ALTER TABLE '
            , table_name, ' '
            , 'MODIFY COLUMN ', column_name, ' '
            , column_type, ' '
            , CASE 
                WHEN character_set_name IS NOT NULL 
                    THEN CONCAT('CHARACTER SET ', character_set_name, ' COLLATE ', collation_name, ' ')
                ELSE ' '
              END
            , CASE 
                WHEN is_nullable = 'NO' AND column_default IS NULL 
                    THEN 'NOT NULL '
                WHEN is_nullable = 'NO' AND column_default IS NOT NULL 
                    THEN CONCAT('DEFAULT \'', column_default, '\' NOT NULL ')
                WHEN is_nullable = 'YES' THEN 'DEFAULT NULL '
              END
            ) AS cmd
            , column_name AS columnname
        FROM INFORMATION_SCHEMA.COLUMNS 
        WHERE table_schema = 'test'
        AND table_name = 'alphabet'
        ORDER BY columnname
    ) t1
    INNER JOIN (
        SELECT @rankmain:=@rankmain+1 AS rownum, position FROM (
            SELECT 0 AS rownum, 'FIRST' AS position
                , '' AS columnname
            UNION
            SELECT @rank:=@rank+1 AS rownum, CONCAT('AFTER ', column_name) AS position
                , column_name AS columnname
            FROM INFORMATION_SCHEMA.COLUMNS
                WHERE table_schema = 'test'
                AND table_name = 'alphabet'
            ORDER BY columnname
            LIMIT limitoffset
        ) inner_table
    ) t2 ON t1.rankalter = t2.rownum

    ;

END//
DELIMITER ;

If anyone thinks/sees that I'm missing to include any important column attributes in the ALTER command, please hesitate not and mention it! Now to the next procedure. This one just executes the commands following the order of column id from the loadcommands table. :

-- PROCEDURE TO RUN EACH REORDERING COMMAND
DELIMITER //
CREATE PROCEDURE reorder_executecommands ()
BEGIN
    DECLARE sqlcommand VARCHAR(1000);
    DECLARE isdone INT DEFAULT FALSE;

    DECLARE reorderCursor CURSOR FOR
    SELECT sqlcmd FROM loadcommands ORDER BY id;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET isdone = TRUE;

    OPEN reorderCursor;
    read_loop:LOOP
        FETCH reorderCursor INTO sqlcommand;

        IF isdone THEN
            LEAVE read_loop;
        END IF;

        SET @sqlcmd = sqlcommand;
        PREPARE stmt FROM @sqlcmd;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;          

    END LOOP read_loop;

    CLOSE reorderCursor;    
END//
DELIMITER ;

The SQL is long, so if someone can point out ways (and has tested them) to make this shorter I'd gladly do it, but for now, this at least works on my end. I also didn't need to put dummy data in the alphabet table. Checking the results can be done using the SHOW... command.

The last part:

-- TO TEST; AFTER RUNNING DDL COMMANDS:

SHOW CREATE TABLE alphabet;     -- SEE ORIGINAL ORDER
CALL reorder_loadcommands();    -- PREPARE COMMANDS
CALL reorder_executecommands(); -- RUN COMMANDS
SHOW CREATE TABLE alphabet;     -- SEE NEW ORDER

Perhaps later on I could make reorder_loadcommands dynamic and accept table and schema parameters, but I guess this is all for now..

sort describe table foo output by field alphabetically

DESCRIBE and SHOW COLUMNS don't have an option to sort their results. You can read more about them here: https://dev.mysql.com/doc/refman/5.7/en/show-columns.html

If you want a customized list of columns of a table, you can query the INFORMATION_SCHEMA.

SELECT COLUMN_NAME, COLUMN_TYPE, COLUMN_DEFAULT, IS_NULLABLE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA='mydatabase' AND TABLE_NAME='mytable';

Read more about this table here: https://dev.mysql.com/doc/refman/5.7/en/columns-table.html

How to get column names as the order in table

There should be a column that has ordinal position.

SELECT
    COLUMN_NAME
FROM
    INFORMATION_SCHEMA.COLUMNS
WHERE
    TABLE_NAME = 'prodotti'
ORDER BY ORDINAL_POSITION

Mysql: Select all values alphabetically between two strings in a table

If you only want the ID's you need:

select id from myTable where name between 'beatrice' and 'tom' order by name;

MySQL ALTER TABLE sort columns alphabetically

There is no way for changing column order in a mysql table. However, you can create a new table with the columns in your order, using for example:

CREATE TABLE newtable SELECT a,b,c,d,e,f,g FROM old_table_name

This way, your newly created table will have columns in your defined order, and you can drop the old table and rename the newtable to old name.

In order to create the above mentioned query, you just need to get column names from your old table and sort them, to do that programatically you can use something like this:

SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA='database_name' AND TABLE_NAME = 'old_table_name' 
ORDER BY column_name

---

Related Topics