CODEWITHSUNDEEP
androidsqlite
Troy Stopera
Troy Stopera

Reputation: 302

Android SQLite select maximum value that is less than another value

Example Schema: Table(Id, Time).

How can I go about selecting the row with the maximum Time that is less than another given Time (i.e. the previous time)?.

I was thinking something along the lines of: 

SELECT MAX(Time) FROM SELECT * FROM Table WHERE Time < x

but I can't remember the proper syntax or if there is another more efficient way.

Upvotes: 1

Views: 627

Answers (2)

blueaac
blueaac

Reputation: 147

Tried and tested. This works

BEGIN TRANSACTION;

CREATE TABLE DATES(Id integer PRIMARY KEY, Time DATETIME);

INSERT INTO DATES VALUES(1, DATETIME("2015-05-21 10:07:00"));
INSERT INTO DATES VALUES(2, DATETIME("2015-05-21 10:08:00"));
INSERT INTO DATES VALUES(3, DATETIME("2015-05-21 10:10:00"));
INSERT INTO DATES VALUES(4, DATETIME("2015-05-21 10:12:00"));
INSERT INTO DATES VALUES(5, DATETIME("2015-05-21 10:09:00"));
COMMIT;

SELECT * FROM DATES;


SELECT MAX(Time) FROM DATES WHERE Time < DATETIME("2015-05-21 10:09:42");

Upvotes: 1

JAWAHAR GANESH
JAWAHAR GANESH

Reputation: 36

select MAX(TIME) from TABLE where Time >VALUE;

I assume that you are storing TIME as a number.

Because SQLite does not have a storage class for Time.

If you are using Time function,

select MAX(time(TIME)) from TABLE where time(TIME) > time(VALUE);

Upvotes: 0

Related Questions