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

Reply via email to