John Elrick wrote: > 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? >
Solved it on my own. Doh moment.: select x_next.x_id from x_sorter x_current, x_sorter x_next where x_current.x_id = ? and x_next.sort_key3 > x_current.sort_key3 and x_next.sort_key2 >= x_current.sort_key2 and x_next.sort_key1 >= x_current.sort_key1 order by x_next.sort_key1, x_next.sort_key2, x_next.sort_key3 _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
