1

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.

Srichand Yella
  • 4,218
  • 2
  • 23
  • 24

4 Answers4

5

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).

Jhovanny
  • 139
  • 2
  • Ooh I was just editing to add a similar suggestion but that's a clever way to do it... – Paul D'Ambra Sep 24 '12 at 21:24
  • Thank you for the answer. This is what I did and it didn't work: select 1, * from teams where league='league' and name='name' union all select 2, * from teams where league='league' and name!='name' order by 2, name desc; It didn't work. – Srichand Yella Sep 27 '12 at 14:55
1

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.

Community
  • 1
  • 1
Paul D'Ambra
  • 7,629
  • 3
  • 51
  • 96
1

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.

xbakesx
  • 13,202
  • 6
  • 48
  • 76
1

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.

digitaljoel
  • 26,265
  • 15
  • 89
  • 115