A bit of bitterness here: Jim, do you know that SELECT doesn't guarantee rows to be returned in any particular order unless ORDER BY clause is used? It's even not guaranteed that the same SELECT without ORDER BY issued twice will return rows in the same order. So when you do SELECT * FROM phonebook ORDER BY last_name, first_name you are guaranteed to get ordered rows. But when you do SELECT * FROM phonebook_order database engine is not obligated to give you rows in any particular order.
And one more thing: please don't mix rowid with row number. They are completely different things. You want row number for some particular row so you have to count rows in your application or to issue appropriate select for that. Appropriate select would be like this: SELECT COALESCE(COUNT(*), 0) + 1 from phonebook WHERE last_name < 'Smith' OR (last_name = 'Smith' AND first_name < 'John'); But here you have to think of how to assign row numbers to different people named John Smith. Pavel On Fri, May 28, 2010 at 8:19 PM, Jim Terman <jter...@tivo.com> wrote: > Say I have a table of phone numbers > > CREATE TABLE phonebook (first_name TEXT, last_name TEXT, phone_number TEXT); > > I want to sort this by name, so I create a view > > CREATE VIEW phonebook_order AS SELECT first_name, last_name, > phone_number FROM phonebook ORDER BY last_name, first_name; > > Now on the table phonebook I can do a query: > > SELECT rowid FROM phonebook where last_name = "Smith" and first_name = > "John"; > > which will gave me the row number of John Smith. > > How do I do this for the view phonebook_order? > > Nearest I can determine would be to run the command > > SELECT COUNT(*) from phonebook_order WHERE last_name <= "Smith" AND > first_name <= "John"; > > Is there an easier way? > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
