On 11/13/2016 6:17 PM, Mike King wrote:
Sorry to reply again so soon. I'm just playing about with your query. The values in Value1 and Value2 are not unique so I don't think your method would work.
Well, in this case, your problem is under-specified. How do you plan to assign a number to a row that's part of a group of rows all sharing the same Value2?
One possible approach is to use ID to break ties: select count(*) from Test t1 join Test t2 where t2.ID = 1 and (t1.Value2 < t2.Value2 or (t1.Value2 = t2.Value2 and t1.ID <= t2.ID)); That's equivalent to "order by Value2, ID" in your temporary table approach. -- Igor Tandetnik _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users