-1

I have Some data in SQLite database in android. I want to retrieve data from database using WHERE clause Using LIKE and NOT LIKE Conditions. I am combining two statements using UNION ALL Command. For that the queries are

SELECT * FROM TABLENAME WHERE column_name LIKE SOMTHING.

This will return some rows. and then i am writing query 

SELECT * FROM TABLENAME WHERE column_name NOT LIKE SOMTHING.

this will return remaining rows.

I want returned items of first query at first then 2nd query items afterwards. This can be achieved by using UNION ALL command. So Query is Like

 db.rawQuery("SELECT * FROM TABLENAME WHERE column_name LIKE SOMTHING UNION ALL SELECT * FROM TABLENAME WHERE column_name NOT LIKE SOMTHING")

But I want 2nd query items in sorted order along with and after first query item/items. So i wrote the Query like

 db.rawQuery("(SELECT * FROM TABLENAME WHERE column_name LIKE SOMTHING) UNION ALL (SELECT * FROM TABLENAME WHERE column_name NOT LIKE SOMTHING ORDER BY column_name)")

I NEED ALL DATA FROM THAT TABLE BUT FIRST I WANT ROWS RETURNED BY FIRST QUERY THAT NEED NOT TO BE SORTED, BUT AFTER THOSE ROWS i WANT ALL ROWS(where not like) IN SORTED ORDER, I am getting all data in a sorted form, at first position it is not returning rows for first query(before union all)

can any one help me on this. Thanks in advance.

Charan
  • 63
  • 1
  • 3
  • 9
  • What is actually not working? In your last code you're only sorting the query after union but not the query before union, is that the problem? – Chor Wai Chun Jun 04 '13 at 06:45
  • I NEED ALL DATA FROM THAT TABLE BUT FIRST I WANT ROWS RETURNED BY FIRST QUERY THAT NEED NOT TO BE SORTED, BUT AFTER THOSE ROWS i WANT ALL ROWS(where not like) IN SORTED ORDER, I am getting all data in a sorted form, at first position it is not returning rows for first query(before union all) – Charan Jun 04 '13 at 06:52
  • 1
    Firstly you may need to understand that capping sentence this way seems quite rude. Then, if you're copying exactly what you coded in your application, you're missing 1 closing bracket at the end of your query, check if adding it solves your problem. – Chor Wai Chun Jun 04 '13 at 06:55
  • I am sorry for my mistake. i am not copying this from my code. Now i edited my post. – Charan Jun 04 '13 at 07:06
  • The code is suppose to work, at least it worked in my application. Is the so called "column_name" a primary key in text? Because if it is, the database itself might use it as something like index mechanism so it will be automatically sorted alphabetically. Not very sure tough, if this matches your case, try use some auto incremental ID as the primary key instead. – Chor Wai Chun Jun 04 '13 at 07:19
  • i will try and i will let you know. Thank you – Charan Jun 04 '13 at 07:25

1 Answers1

4

You can order only the entire result.

This means that you have to introduce a virtual column to be able to differentiate the records from the subqueries:

SELECT *, 0 AS SubQueryNr FROM TableName WHERE column_name LIKE ?
UNION ALL
SELECT *, 1               FROM TableName WHERE column_name NOT LIKE ?
ORDER BY SubQueryNr, column_name

Alternatively, just use the LIKE condition for ordering:

SELECT * FROM TableName
ORDER BY column_name LIKE ? DESC
CL.
  • 173,858
  • 17
  • 217
  • 259
  • Thanks for your suggestions. we sorted the data in server from where we are getting data to database. So no need to sort in queries. Thanks again for your help – Charan Jun 04 '13 at 13:33