SQL request with case in order by throws "(1) 1st ORDER BY term does not match any column in the result set"

Question

I've got a small problem with an SQL cursor. I'm trying to execute an SQL command on android. I'm trying to sort the returned content by cases, but it seems like the system doesn't accept the returned values (?!) I've tried everything! Do you have the solution ? ;)

cursor = db.rawQuery(c, null);
String c = "SELECT * FROM characters WHERE UPPER(descriptions) LIKE '%" + 
    TextUtils.join("%", arr) + "%' UNION ALL SELECT * FROM words WHERE
    UPPER(descriptions) LIKE '%" + TextUtils.join("%", arr) + "%'
    ORDER BY CASE WHEN UPPER(descriptions) LIKE '" + s + "' THEN 1
    WHEN UPPER(descriptions) LIKE '" + s + "|%' THEN 2 WHEN
    UPPER(descriptions) LIKE '%|" + s + "|%' THEN 2 WHEN UPPER(descriptions)
    LIKE '%|" + s + "' THEN 2 WHEN UPPER(descriptions)
    LIKE '%" + TextUtils.join(" ", arr) + "%' THEN 3 ELSE 4 END, descriptions ASC";

Do I need to sort the command like this?

SELECT * FROM characters WHERE UPPER(descriptions) LIKE '%" + TextUtils.join("%", arr) + "%' 
UNION ALL
SELECT * FROM words WHERE UPPER(descriptions) LIKE '%" + TextUtils.join("%", arr) + "%'                                     
ORDER BY
    CASE
        WHEN UPPER(descriptions) LIKE '" + s + "' THEN 1          
        WHEN UPPER(descriptions) LIKE '" + s + "|%' THEN 2 
        WHEN UPPER(descriptions) LIKE '%|" + s + "|%' THEN 2 
        WHEN UPPER(descriptions) LIKE '%|" + s + "' THEN 2 
        WHEN UPPER(descriptions) LIKE '%" + TextUtils.join(" ", arr) + "%' THEN 3 
        ELSE 4 
    END,
descriptions ASC

Thanks ;)

EDIT: An SQL command after inserting the search term "a test"

SELECT * FROM characters WHERE UPPER(descriptions) LIKE '%A%TEST%'
UNION ALL
SELECT * FROM words WHERE UPPER(descriptions) LIKE '%A%TEST%'
ORDER BY
    CASE
        WHEN UPPER(descriptions) LIKE 'A TEST' THEN 1
        WHEN UPPER(descriptions) LIKE 'A TEST|%' THEN 2
        WHEN UPPER(descriptions) LIKE '%|A TEST|%' THEN 2
        WHEN UPPER(descriptions) LIKE '%|A TEST' THEN 2
        WHEN UPPER(descriptions) LIKE '%A TEST%' THEN 3
        ELSE 4
    END,
descriptions ASC

table schema:

characters (t TEXT,s TEXT,jy TEXT,descriptions TEXT)
words (t TEXT,s TEXT,jy TEXT,descriptions TEXT)

Can you print out the SQL *after* the variables have been substituted? And are you sure there is a column called `descriptions` in the `characters` table? — Gordon Linoff, Jan 19 '14 at 22:13
I am 100% sure, that the column "descriptions" exists. I edited the main post with a substituted sql ;) thx — Phil Plückthun, Jan 19 '14 at 22:19
What if in your arr or s you have a value containing ' ? OK, maybe you don't. — peter.petrov, Jan 19 '14 at 22:21
Like you can see in the substituted sql the error occurs even if the variable don't contain ' — Phil Plückthun, Jan 19 '14 at 22:23
Posting the schema of your words and characters tables would help troubleshooting too. — peter.petrov, Jan 19 '14 at 22:24
I am not too familiar with SQL. I don't know what an alias for an order by statement should look like. I added the table schema. — Phil Plückthun, Jan 19 '14 at 22:28
You should consider using FTS for your tables to improve performance after you solved your problem. — AlexS, Jan 20 '14 at 01:41

CL. · Accepted Answer · 2014-01-20T08:31:29.277

7

When you are SELECTing records from a single table, you can use anything from those records for sorting.

However, when you are combining multiple queries with UNION, the sorting is done on the entire result, so you must use some column from the result for ordering. In this case, this means that you must move the calculation into the query itself:

SELECT t, s, jy, descriptions, CASE ... END AS ordernr FROM ...
UNION ALL
SELECT t, s, jy, descriptions, CASE ... END AS ordernr FROM ...
ORDER BY ordernr,
         descriptions

edited Jan 20 '14 at 08:31

answered Jan 19 '14 at 22:38

CL.

173,858
17
217
259

I think the problem still lies in the CASE ... END part. Even if I replace the order by descriptions command with '4', or just omit it the error stays. The error states also "(1) 1st ORDER BY term...", so it clearly states a problem in the first part. – Phil Plückthun Jan 19 '14 at 22:48
@CL. . . . Your description is just clearly wrong. `Union` (and `union all`) take the column names from the first table/query in the union. The `order by` at the end of the statement then applies to the union'ed results. – Gordon Linoff Jan 19 '14 at 23:24
@PhilPlückthun Yes, the first term had the same problem; updated. – CL. Jan 20 '14 at 08:32

score 0 · Answer 2 · edited May 23 '17 at 12:09

0

See this post and try specifying all columns in your select statements.

edited May 23 '17 at 12:09

Community

1
1

answered Jan 20 '14 at 01:49

AlexS

5,295
3
38
54

score 0 · Answer 3 · answered Jun 03 '21 at 13:14

The real reason seems here :

In standard SQL, the ORDER BY clause must be either (1) a positive integer that means sort by the corresponding column or (2) an exact copy of one of the expressions that defines the column. The ORDER BY may not have arbitrary expressions, in standard SQL. Many RDBMSes (including SQLite) extend this so that you can have arbitrary expressions in the ORDER BY clause, but in the case of a UNION query, that enhancement does not apply (for technical reasons) and the original standard-SQL restriction is in force.

SQL request with case in order by throws "(1) 1st ORDER BY term does not match any column in the result set"

3 Answers3