create the primary key index ordered properly CREATE TABLE t (..., PRIMARY KEY ( a ASC, b DESC)...); SELECT b FROM t WHERE a = ? LIMIT 1;
If you insist on using a partial index for this (for example if each a has a lot of b entries) you could add a field b_is_max and keep it current using triggers. CREATE INDEX t_max ON t (a,b) WHERE b_is_max; SELECT b FROM t WHERE a=? AND b_is_max; CREATE TRIGGER t_ins AFTER INSERT ON t WHEN new.b > (SELECT b FROM t WHERE a = new.a AND b_is_max) BEGIN UPDATE t SET b_is_max = 0 WHERE a = new.a AND b_is_max; UPDATE t SET b_is_max = 1 WHERE a = new.a AND b = new.b; END; CREATE TRIGGER t_del BEFORE DELETE ON t WHEN old. b_is_max) BEGIN UPDATE t SET b_is_max = 0 WHERE a = old.a AND b = old.b); UPDATE t SET b_is_max = 1 WHERE a = old.a AND b = (SELECT max(b) FROM t WHERE a = old.a and b < old.b); END; CREATE TRIGGER t_upd_new AFTER UPDATE ON t WHEN new.b > (SELECT b FROM t WHERE a = new.a AND b_is_max) BEGIN UPDATE t SET b_is_max = 0 WHERE a = new.a AND b_is_max; UPDATE t SET b_is_max = 1 WHERE a = new.a AND b = new.b; END; CREATE TRIGGER t_upd_old BEFORE UPDATE ON t WHEN old.b_is_max) BEGIN UPDATE t SET b_is_max = 0 WHERE a = old.a AND b = old.b); UPDATE t SET b_is_max = 1 WHERE a = old.a AND b =(SELECT max(b) FROM t WHERE a = old.a and b < old.b); END; Triggers shown are untested. -----Ursprüngliche Nachricht----- Von: Baruch Burstein [mailto:bmburst...@gmail.com] Gesendet: Montag, 29. Dezember 2014 09:34 An: General Discussion of SQLite Database Betreff: [sqlite] Partial index to find maximum Hi, I have a table with a 2 column PK, say 'a' and 'b'. I need to find, for a given value of 'a', the highest matching 'b'. The query itself it simple: SELECT max(b) FROM t WHERE a=:whatever To speed this up, I would add an index on 'a'. Now, the question is is there some way to tell the index that I am only interested in the maximum value of b? For example, for the following table: a|b 1|1 1|2 2|2 2|3 I only need the index to contain the rows (1,2) and (2,3). The docs for partial indexes say that they can't contain functions (like max()). Any suggestions? -- ˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users