Currently my query looks like this:
SELECT name FROM items
WHERE something = "foo"
AND name > (SELECT name FROM items WHERE name = ?)
GROUP BY name
ORDER BY name ASC
LIMIT 1
(found it here)
It seems to work, but if I change > to < (to get previous record), I get no results, even though I know there are :(
What am I doing wrong?
name is a TEXT field, but I'd like to be able to sort by any other field, so I want my query to work with any type.
To get the previous row in this way, you will have order the names in descendinng order instead of ascending, otherwise you will always get the first name. Also: In this case it is better to use select distinct instead of group by:
SELECT DISTINCT name FROM items
WHERE something = 'foo'
AND name < (SELECT name FROM items WHERE name = ...)
ORDER BY name DESC
LIMIT 1
This works on sqlfiddle: http://sqlfiddle.com/#!7/a9f68/3/0
Trying using strcmp() as detailed at http://www.sqlite.org/datatypes.html
I think one issue for you might be that your > is not working as you suspect in the first instance.
