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

Reply via email to