CODEWITHSUNDEEP
androidsqlsqliteselect
Srichand Yella
Srichand Yella

Reputation: 4246

How do I combine two queries into one?

This is for Android SQLite. I have two queries like this:

select * from table where name='name';

and

select * from table where name!='name' order by name;

I want to create a statement which combines these two queries. I tried union all but I can't do order by one statement and then combine. I tried this:

select * from table where name='name'
union all
select * from table where name!='name' order by name;

All it did is to combine the queries and then order by name. I don't want that. I want to do order by on the second statement first and then combine them.

To put the question differently, here is my data:

Name

a
b
c
d
e
f
g
h
i
j

But I want the output to be:

Name

g
a
b
c
d
e
f
h
i
j

I want to get one row to the top and then order the rest of the rows. Any help is appreciated.

Upvotes: 1

Views: 1709

Answers (4)

digitaljoel
digitaljoel

Reputation: 26574

Can't test it right now, but something like this should work:

select t.*, case when name = 'name' then 0 else 1 as o from table t order by o, name;

Then you don't have the two selects nor the union. Assuming you can use a case statement in sqlite on android.

Upvotes: 1

xbakesx
xbakesx

Reputation: 13490

According to the SQLLite docs this cannot be done with a UNION or UNION ALL because those operations must be performed on a simple select, (ones without Order by).

http://www.sqlite.org/lang_select.html

There's probably a very clever way to do this that I don't know, which generally leads me to just do two queries and combine the results in java.

[EDIT] And Jhovanny has the very clever way to do it.

Upvotes: 1

Jhovanny
Jhovanny

Reputation: 139

No need to use temporary tables, you need to add an additional column to sort on. Something like this:

select 1, * from table where name='name'
union all
select 2, * from table where name!='name' 
order by 1, name;

I don't have a sqlite install right now, but this trick should work. (you may have to add an alias to the first column).

Upvotes: 5

Paul D'Ambra
Paul D'Ambra

Reputation: 7814

Unless there is some other attribute of the table you can use to provide sorting that allows a join between the two selects as in How to combine two sql queries? then I think you'll have to store the result of the query that should float to the top in a temporary table and then add the sorted results to that table before storing it.

I've never used temporary tables in Android so can't provide an example but as far as I'm aware it's possible.

I'd recommend running the two queries separately and then combining the results in code if that's possible in your situation.

Upvotes: 1

Related Questions