CODEWITHSUNDEEP
androidsqlsqlite
The.Anti.9
The.Anti.9

Reputation: 44718

SQLite query: selecting the newest row for each distinct contact

I'm working with the Android SQLite db, and I have a table with messages in it. Each message has a ContactId. I need to get a list of the newest message associated with each ContactId.

My query looks like this so far:

SELECT * FROM messages GROUP_BY ContactId HAVING ( COUNT(ContactId) = 1 ) ORDER_BY MessageTime DESC

However when I run the query I get this exception:

near "ContactId": syntax error: , while compiling: SELECT * FROM messages GROUP_BY ContactId HAVING ( COUNT(ContactId) = 1 ) ORDER_BY MessageTime DESC

Here's the table definition in case it helps:

create table messages (_id integer primary key autoincrement, ContactId text not null, ContactName text not null, ContactNumber text not null, isFrom int not null, Message text not null, MessageTime int not null);

Upvotes: 1

Views: 691

Answers (2)

satur9nine
satur9nine

Reputation: 15072

NOTE: My answer below does not appear to be working as of SQLite 3.7.5, I suggest using the "JOIN" query suggested by Larry.

You are close. What you need to do is sort all the records first, using a subquery table, and then group them. The values that are returned in the result set will be from the last row in each group. So you actually want newer messages to appear at the bottom if you are trying to get the newset message. The "GROUP BY" already ensures you get one row per ContactId.

SELECT * FROM (SELECT * FROM messages ORDER BY MessageTime) GROUP BY ContactId

The HAVING clause is not needed. I haven't used it before but according to the docs the HAVING clause will discard whole groups that don't match, but it doesn't sound like you want any groups discarded, you want results from every ContactId.

Also note there is no underscore in "ORDER BY" or "GROUP BY".

Upvotes: 2

Larry Lustig
Larry Lustig

Reputation: 51000

Here are two ways to do it.

This query builds a list of the most recent times for each user, then JOINs that back to the message table to get the message information:

 SELECT M1.* FROM messages M1 JOIN 
   (SELECT ContactId, MAX(MessageTime) AS MessageTime FROM messages GROUP BY ContactId) M2
   ON M1.ContactID = M2.ContactID AND M1.MessageTime = M2.MessageTime;

This query does something slightly different. It looks at each message and asks if there exists any later message for the same contact. If not, the row must be the most recent one:

 SELECT M1.* FROM messages M1
    WHERE NOT EXISTS (SELECT * FROM M2 
      WHERE M2.ContactID = M1.ContactID AND M2.MessageTime > M1.MessageTime)

Upvotes: 2

Related Questions