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:[email protected]]
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
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
