Mysql Order By Primary Key Dynamically In Desc Order

I am trying to query a table in descending order based on its primary key column. Here is the query: SELECT * FROM fdmsus_demo.vitalstats ORDER BY ( SELECT `COLUMN_NAME` FROM `inf

Solution 1:

Try applying desc to inner query .

Solution 2:

Try this it will work

set@Query1=Concat('SELECT * FROM fdmsus_demo.vitalstats ORDER BY ', (SELECT `COLUMN_NAME`
    FROM `information_schema`.`COLUMNS`
    WHERE (`TABLE_SCHEMA` ='fdmsus_demo')
      AND (`TABLE_NAME` ='vitalstats')
      AND (`COLUMN_KEY` ='PRI')),' desc limit 10');

 PREPARE stmt FROM@Query1; 
 EXECUTE stmt;  

Solution 3:

You have to write prepare statement

SET@col='';
SELECT `COLUMN_NAME` INTO@colFROM `information_schema`.`COLUMNS`
WHERE (`TABLE_SCHEMA` ='fdmsus_demo')
  AND (`TABLE_NAME` ='vitalstats')
  AND (`COLUMN_KEY` ='PRI');
SET@q= CONCAT('SELECT * FROM fdmsus_demo.vitalstats ORDER BY ',@col,' DESC LIMIT 10');
PREPARE stmt FROM@q;
EXECUTE stmt;
DEALLOCATEPREPARE stmt;

Solution 4:

SELECT * FROM fdmsus_demo.vitalstats
ORDERBY 
( SELECT `COLUMN_NAME`
FROM `information_schema`.`COLUMNS`
WHERE (`TABLE_SCHEMA` = 'fdmsus_demo')AND (`TABLE_NAME` = 'vitalstats')AND (`COLUMN_KEY` = 'PRI') ORDERBY COLUMN_NAME DESC LIMIT 10;
) ORDERBY COLUMN_NAME DESC LIMIT 10;

This should do. You are missing on which you need to apply desc function in the inner query.