MySQL ORDER BY field only if field exists

Question

In MySQL I want to sort by a field if that field exists as a column in the table. If that field does not exist I want the query to ignore that segment of the query. If I try something like

SELECT
    post.*
FROM post AS post
WHERE post.postid IN (9818,9814,9815)
ORDER BY IFNULL(post.vote_count,0) > 5 DESC;

at the end of my query I get the error:

#1054 - Unknown column 'post.vote_count' in 'order clause'

The table structure for post has postid, threadid, and title fields. How should I write this query?

(I've seen Ordering by a field, only if it exists. That answer doesn't work for me since I can't add the earlier portion of the query, "IFNULL(post.vote_count,0) as newvotecount".)

You need to use dynamic SQL for this (`prepare` and `execute`). — Gordon Linoff, Nov 16 '14 at 12:41
@juergend, let me know if how I have edited the question helps. — zylstra, Nov 16 '14 at 17:22
But you have no `vote_count` column in your table. What do you expect the DB to do with that? — juergen d, Nov 16 '14 at 17:23
I expect the query to skip the ORDER by element of the query. — zylstra, Nov 16 '14 at 17:29
Again - you have no `vote_count` column in your table! Please add example data and expected output. — juergen d, Nov 16 '14 at 17:31
The fact that I don't have vote_count column in my table is irrelevant. SELECT post.* FROM post AS post WHERE post.postid IN (9818,9814,9815) works. — zylstra, Nov 16 '14 at 17:32
Irrelevant? Hardly. This kind of problem typically points to more serious problems about the design of your schema. You should know what columns exist in your tables. — Strawberry, Nov 16 '14 at 17:55

score -2 · Answer 1 · answered Nov 16 '14 at 11:24

-2

You can not use field in query if it is not exists in table (or as alias in query)

answered Nov 16 '14 at 11:24

Volk

46
4

That Possible using exist – Sureshkumar Panneerselvan Nov 16 '14 at 11:51
Exist can be used to see if another recordset exists. Not column. – Volk Nov 16 '14 at 11:54
Kindly check my answer to this post – Sureshkumar Panneerselvan Nov 16 '14 at 11:54
Yes, you could check column existance in query, thats true. But you still cant use not existent column in a query – Volk Nov 16 '14 at 12:03

score -2 · Answer 2 · edited May 23 '17 at 11:45

-2

In Oracle u can do this by using following

    SELECT * FROM table_name
    ORDER BY CASE WHEN EXISTS (select 1
                              FROM ALL_TAB_COLUMNS 
                              WHERE lower(column_name) IN lower('column1_name') AND lower(table_name) = lower('table_name')) THEN column1_name
                   ELSE column2_name
                   END;

Same way u replace the Oracle System table with Mysql System Table

For Mysql :

    SELECT DISTINCT TABLE_NAME 
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE COLUMN_NAME IN ('columnA','ColumnB')

    AND TABLE_SCHEMA='YourDatabase';

Ref

edited May 23 '17 at 11:45

Community

1
1

answered Nov 16 '14 at 11:43

Sureshkumar Panneerselvan

749
6
22

wat issue u r facing? – Sureshkumar Panneerselvan Nov 17 '14 at 02:02
If the column name doesn't exist you will get an error to the effect that `column2_name` does not exist. – Gordon Linoff Nov 17 '14 at 03:12

MySQL ORDER BY field only if field exists

2 Answers2