CODEWITHSUNDEEP
sqliteandroid-sqlite
Alexander Farber
Alexander Farber

Reputation: 23048

Select a record from SQLite table - and check if its timestamp is the newest one

In an Android game I store user info retrieved from different social networks in an SQLite table - 

create table table_social (
        id text primary key,
        social integer not null, /* Facebook = 1, Google+ = 2, Twitter = 3, ... */
        first_name text not null,
        photo text null,
        city text null,
        stamp datetime default current_timestamp /* indicates if it's "main" profile */
);

and indicate the "main" player profile (the one displayed in the app navigation drawer and to the remote game partners) by the newest timestamp:

app screenshot

My question:

Is it please possible to select a certain record from such a table and also indicate if it has the newest timestamp - in the same SQL command?

(I'd like to use the latter information to enable/disable the "Set as main profile" button in my app).

Here is what I have tried at the Android SDK's sqlite3.exe prompt -

First create the table and fill it with 3 records:

C:\Users\user1\AppData\Local\Android\Sdk\platform-tools>sqlite3.exe
SQLite version 3.8.10.2 2015-05-20 18:17:19
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table table_social (
   ...>         id text primary key,
   ...>         social integer not null, /* Facebook = 1, Google+ = 2, Twitter = 3, ... */
   ...>         first_name text not null,
   ...>         photo text null,
   ...>         city text null,
   ...>         stamp datetime default current_timestamp
   ...> );
sqlite> insert into table_social (id, social, first_name) values (1, 1, "Alex on Facebook");
sqlite> insert into table_social (id, social, first_name) values (2, 2, "Alex on Google+");
sqlite> insert into table_social (id, social, first_name) values (3, 3, "Alex on Twitter");

Then I am able to retrieve the "main" player profile:

sqlite> select * from table_social order by stamp desc limit 1;
3|3|Alex on Twitter|||2015-10-21 10:47:40

And I am able to retrieve the Facebook user data:

sqlite> select *, stamp=max(stamp) from table_social where social=1;
1|1|Alex on Facebook|||2015-10-21 10:47:18|1

But how to combine these 2 commands please?

I have tried the following, but it returns 1 (while it should return false):

sqlite> select *, max(stamp)=stamp from table_social where social=1;
1|1|Alex on Facebook|||2015-10-21 10:47:18|1

Also I have tried a sub-select query:

sqlite> select *, stamp=(select * from table_social order by stamp desc limit 1) from table_social where social=1;
Error: only a single result allowed for a SELECT that is part of an expression

Upvotes: 0

Views: 263

Answers (1)

CL.
CL.

Reputation: 180270

A scalar subquery must return a single value. (The error message already told you this.) So replace (select * ...) with (select stamp ...).

Upvotes: 1

Related Questions