How to sort by first name and last name in search bar - Sqlite

Question

I have an Android application that should replace the Android native contacts.

I want to add the possibility to the user to search a user based on character constraint.

For example:

this is my contacts table:

id  firstName  lastName
1.    Smith      Jean
2.    allen      carr
3.               zetter
4.    john       Stewart
5.    Smith      Allen
6.    Smith      Davey
7.               Smitten
8.    barney     saltzberg

If the user enters the character 's', I want to give him all the contacts statrting with 's' in their first name OR last name, sorted by the first name first and then the last name. From the table before the result I want to get is:

id  firstName  lastName
1.    Smith      Allen
2.    Smith      Davey
3.    Smith      Jean
4.    barney     saltzberg
4.               Smitten
5.    john       Stewart

UPDATE: The problem is when the First name is equals to NULL, the sort is not working and the row is showed before it should. I tried marcin's answer and it's give me the wrong result.

I tried the following:

String selection = PeopleDataBase.COLUMN_FIRST_NAME + " LIKE '" + constraint + "%' OR " + PeopleDataBase.COLUMN_LAST_NAME + " LIKE '" + constraint + "%'";

Cursor cur = db.query(PeopleDataBase.TABLE_PEOPLE, null, selection, null, null, null, null);

I thought to achieve this by two different queries, one for first name and one for last name and then concatenate them to one cursor, but I'm sure there is a better solution.

UPDATE: I also tried to sort in the following way with no success.

   Cursor cur = db.query(PeopleDataBase.TABLE_PEOPLE, null, selection, null, null, null, PeopleDataBase.COLUMN_FIRST_NAME + "," + PeopleDataBase.COLUMN_LAST_NAME);

Do you have a better solution?

Hello all, the answers still not helped me. I think I'll do it in the hard way, two different queries and then concatenate the answers. — Ofir A., Jun 17 '13 at 14:44

score 2 · Answer 1 · answered May 27 '13 at 08:06

Have you tried with an ORDER BY?:

String selection =  
PeopleDataBase.COLUMN_FIRST_NAME + " LIKE '" + constraint + "%' OR " +  
PeopleDataBase.COLUMN_LAST_NAME + " LIKE '" + constraint + "%'" ORDER BY " + 
PeopleDataBase.COLUMN_FIRST_NAME + " ASC, " + PeopleDataBase.COLUMN_LAST_NAME + " ASC";

score 2 · Answer 2 · answered Jun 11 '13 at 12:17

TL;DR

I don't think this is easily possible. Consider relaxing your requirements.

Custom collation sequence

If installing custom collation sequences in Android was easily possible, you'd only need to implement a collation sequence that makes null equal to any value. However, installing collation sequences is not possible using the sqlite Android Java API.

Alternative problems

Can you alter your problem?

Would it be acceptable that null first names get sorted first or last? Then the query could be something along the lines of
```
select * from contacts
  where firstname like 's%' or lastname like 's%'
  order by
    case when firstname like 's%' then 0 else 1 end,
    firstname collate nocase,
    lastname collate nocase;
```
Here the first order by expression sorts by whether first name matched or not and then by first name and last name.
Something else to make the problem easier to solve?

Testing

First, let's prepare our sqlite3 sandbox:

sqlite> create table contacts(id integer primary key, firstname text, lastname text);
sqlite> insert into contacts values(1,'Smith','Jean');
sqlite> insert into contacts values(2,'allen','carr');
sqlite> insert into contacts values(3,null,'zetter');
sqlite> insert into contacts values(4,'john','Stewart');
sqlite> insert into contacts values(5,'Smith','Allen');
sqlite> insert into contacts values(6,'Smith','Davey');
sqlite> insert into contacts values(7,null,'Smitten');
sqlite> insert into contacts values(8,'barney','saltzberg');
sqlite> .head on

Note that you'll get the example results you asked for by simply sorting by lastname:

sqlite> select * from contacts where firstname like 's%' or lastname like 's%' order by lastname collate nocase;
id|firstname|lastname
5|Smith|Allen
6|Smith|Davey
1|Smith|Jean
8|barney|saltzberg
7||Smitten
4|john|Stewart

Obviously this is not what you want so let's add some more test data to the sandbox:

sqlite> insert into contacts values(9,'see','this');
sqlite> select * from contacts where firstname like 's%' or lastname like 's%' order by lastname collate nocase;
id|firstname|lastname
5|Smith|Allen
6|Smith|Davey
1|Smith|Jean
8|barney|saltzberg
7||Smitten
4|john|Stewart
9|see|this

Now with this additional test data row, the alternative problem solution mentioned above would return:

sqlite> select * from contacts where firstname like 's%' or lastname like 's%' order by     case when firstname like 's%' then 0 else 1 end, firstname collate nocase, lastname collate nocase;
id|firstname|lastname|sortorder
9|see|this|
5|Smith|Allen|
6|Smith|Davey|
1|Smith|Jean|
7||Smitten|
8|barney|saltzberg|
4|john|Stewart|

Alexander Sidikov Pfeif · Accepted Answer · 2013-06-19T12:36:17.703

how do you display the contacts ? in a listview ?

another idea is to load all entries in a listview and than you can filter the list

you can create a custom Listview and implement Filterable

public class YourContactsListAdapter extends BaseAdapter implements Filterable {
  //some methodes to override 

   @Override
    public Filter getFilter() {
        Filter filter = new Filter() {
            @Override
            protected FilterResults performFiltering(CharSequence c) {}
 @Override
            protected void publishResults(CharSequence charSequence, FilterResults filterResults) {}
}
}

maybe the easiest but not best way.. Hopefully I have not misunderstood the question.

//EDIT:

try cursorAdapter not BaseAdapter here is an example... http://tausiq.wordpress.com/2012/08/22/android-list-view-from-database-with-cursor-adapter/

score 1 · Answer 4 · edited May 23 '17 at 11:51

1

case when MyDate is null then 1 else 0 endPeopleDataBase.COLUMN_FIRST_NAME + ", " + PeopleDataBase.COLUMN_LAST_NAME as the last parameter of db.query (instead of null) should work.

EDIT: if you want null values to be last you need to follow: SQL how to make null values come last when sorting ascending

namely:

Cursor cur = db.query(PeopleDataBase.TABLE_PEOPLE, null, selection, null, null, null,
  "case when " + PeopleDataBase.COLUMN_FIRST_NAME + " is null then 1 else 0 end,"
  + PeopleDataBase.COLUMN_FIRST_NAME + ","
  + "case when " + PeopleDataBase.COLUMN_LAST_NAME+ " is null then 1 else 0 end,"
  + PeopleDataBase.COLUMN_LAST_NAME);

edited May 23 '17 at 11:51

Community

1
1

answered May 27 '13 at 08:03

marcin

2,945
2
12
13

I tried it - "I also tried to sort in the following way PeopleDataBase.COLUMN_FIRST_NAME, PeopleDataBase.COLUMN_LAST_NAME with no success. ". – Ofir A. May 27 '13 at 08:05
and what was the result of query: `db.query(PeopleDataBase.TABLE_PEOPLE, null, selection, null, null, null, PeopleDataBase.COLUMN_FIRST_NAME + ", " + PeopleDataBase.COLUMN_LAST_NAME);` ? – marcin May 27 '13 at 08:10
The problem is that it gives me contact with last name only before first name in that qurey. – Ofir A. May 27 '13 at 08:13
http://stackoverflow.com/questions/10254163/android-sql-how-do-you-order-your-sql-query-by-multiple-columns - it says, that separating columns by "," should work. Maybe try adding " ASC" to both columns - but in my opinion it shouldn't change anything. – marcin May 27 '13 at 08:19
You are right it works, but the problem is when I have a contact with last name only - It shows him in the beginning. – Ofir A. May 27 '13 at 08:26
I don't want the null values to be at the end, I want them to be sorted also, meaning to be shown where they need to be. – Ofir A. May 27 '13 at 08:48
What you mean "shown where they need to be."? If you are sorting by name and after that surname, null values (in name column) can be either at the end or at the beginning – marcin May 27 '13 at 09:10
I mean that the last name will sorted also, even if the first name is null. For example: we have the next contacts with null value in the first name: DB, DC, DA. I want them to be shown like this: DA, DB, DC. – Ofir A. May 27 '13 at 09:18
If you use the code from my answer all entries with `first_name=null` will be located at the end, but they will also be sorted by last name – marcin May 27 '13 at 10:35
I tried your answer, all the null values come at the end but they not sorted. – Ofir A. May 27 '13 at 11:00

PositiveLogic · Answer 5 · 2013-06-16T06:53:14.543

For the "desired" example results you've given, this seems like it'll do what you ask:

SELECT * FROM contacts
    WHERE lastname LIKE 's%' OR firstname LIKE 's%'
    ORDER BY LOWER(lastname), UPPER(firstname); -- rudimentary namesorts via "casing"

sqlite> select * from contacts where lastname like 's%' or firstname like 's%'
order by lower(lastname), upper(firstname);
16|sadie||
13|Sarah||
11|sue||
5|Smith|Allen|
6|Smith|Davey|
1|Smith|Jean|
10||Saltzberg|
9|Arney|saltzberg|
8|barney|saltzberg|
14|Carnie|Saltzberg|
7||Smitten|
12|Sally|smitten|
4|john|Stewart|
15|Jon|stewart|
sqlite>

How to sort by first name and last name in search bar - Sqlite

5 Answers5

TL;DR

Custom collation sequence

Alternative problems

Testing