MySQL order by primary key dynamically in DESC ord

Question

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 `information_schema`.`COLUMNS`
WHERE (`TABLE_SCHEMA` = 'fdmsus_demo')
  AND (`TABLE_NAME` = 'vitalstats')
  AND (`COLUMN_KEY` = 'PRI') 
 ) DESC LIMIT 10; 

I am expecting this query to return rows in descending order. However it is not working as expected. I feel DESC is not being applied to the query. The reason I am writing query in this fashion is, I wanted to use this query in my Java code and it will parameterized function like:

public void myFunction(String dbName, String tableName);

Because at run time I will not be knowing what will be the primary key of a table. However I am supplying only 2 parameters dbname and tablename. Hence fetching primary key column name using following sub-query:

( SELECT `COLUMN_NAME`
FROM `information_schema`.`COLUMNS`
WHERE (`TABLE_SCHEMA` = 'fdmsus_demo')
  AND (`TABLE_NAME` = 'vitalstats')
  AND (`COLUMN_KEY` = 'PRI') 
 )

Above query is equivalent to:

SELECT * FROM fdmsus_demo.vitalstats ORDER BY VitalsMasterKey DESC LIMIT 10;

Which returns result in descending order as expected.

Can anyone please help me in correcting the query and get output in descending order. Any help will be appreciated.

Thanks.

Answer 1

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;  

Answer 2

You have to write prepare statement

SET @col = '';
SELECT `COLUMN_NAME` INTO @col
FROM `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;
DEALLOCATE PREPARE stmt;

Answer 3

Try applying desc to inner query .

Answer 4

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') 
ORDER BY COLUMN_NAME DESC LIMIT 10;
) ORDER BY COLUMN_NAME DESC LIMIT 10;

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