On Fri, 28 May 2010 17:19:37 -0700, 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";
The query you propose:
SELECT COUNT(*) FROM phonebook_order
WHERE last_name <= "Smith"
AND first_name <= "John";
will not reliably return the row number of John Smith.
Run this example:
================
CREATE TABLE phonebook (
first_name TEXT,
last_name TEXT,
phone_number TEXT
);
CREATE VIEW phonebook_order AS
SELECT first_name, last_name, phone_number
FROM phonebook
ORDER BY last_name, first_name;
INSERT INTO phonebook (first_name, last_name, phone_number)
VALUES ('Jim','Terman','101004');
INSERT INTO phonebook (first_name, last_name, phone_number)
VALUES ('John','Brown','102001');
INSERT INTO phonebook (first_name, last_name, phone_number)
VALUES ('John','Smith','103003');
INSERT INTO phonebook (first_name, last_name, phone_number)
VALUES ('Simon','Slavin','104002');
.headers on
SELECT rowid
FROM phonebook
WHERE last_name = 'Smith'
AND first_name = 'John';
SELECT * FROM phonebook_order;
SELECT COUNT(*) AS roworder FROM phonebook_order
WHERE last_name <= 'Smith'
AND first_name <= 'John';
================
>Is there an easier way?
Use a counter in your host language.
--
( Kees Nuyt
)
c[_]
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
