I have a situation where I need to retrieve the 'next' item in a table sorted by an arbitrary number of keys. My current planned solution is to create a table for the sorting which is recreated as needed with the appropriate keys. As a simplified example
create table x ( x_id integer primary key, some_value varchar ); create table x_sorter ( x_id integer primary key, sort_key1 integer, sort_key2 integer, sort_key3 integer ); create index x_sorter_idx1 on x_sorter (sort_key1, sort_key2, sort_key3); My goal is, given an x_id I wish to locate in table x_sorter the next x_id given x_sorter_idx1. Data example: x_sorter 1 | 100 | 101 | foo 2 | 100 | 100 | 100 3 | 101 | 100 | 100 4 | 100 | 101 | wibble Given x_id = 1, the next row should be x_id = 4 If there were only one column, this would be a trivial exercise: 1 | 100 2 | 103 3 | 102 4 | 101 select x_id from x_sorter where sort_key1 > (select sort_key from x_sorter where x_id = ?) order by sort_key1 limit 0,1 However, this query becomes problematic if there are more than one sort key as we must somehow account for a different path if the first key is equal (rows 1, 2 and 4 in sample data above). I have thought of and discarded the solution of concatenating the keys into a single string. While this would work for strictly alphanumeric data, it is a customer requirement is that numeric data be treated as numeric for sort purposes. What suggestions for approaching this problem are there which I have overlooked? Thanks, John Elrick Fenestra Technologies _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
