Re: [sqlite] sqlite puzzle

[JP]

Thanks all.  Actually I was just looking for the position of a single 
name.  Based on your feedback, this one works to get the results:


SELECT count(*) FROM clients WHERE name<'foo';

but its performance is directly proportional to the position of the name 
in the table.  For example, searching for Zach takes longer than 
searching for Abigail.  It seems it is not using any index, but rather 
doing a record by record sweep on the 'count'.


jp

Re: [sqlite] sqlite puzzle

[A. Pagaltzis]

And to correct myself one last time:

* A. Pagaltzis <[EMAIL PROTECTED]> [2006-05-03 00:30]:
> Assuming your client names are unique, this should work:
> 
> SELECT
> (
> SELECT
> COUNT(*)
> FROM clients c2
> WHERE c2.name < c1.name
> ORDER BY c2.name
  
  this clause is superfluous
> ) rank,
> c1.name
> FROM clients c1
> ORDER BY rank;
> 
> On MySQL5 and PostgreSQL, it works as intended. Unfortunately,
> SQLite complains that it doesn’t know about `c1.name`. I tried
> to do it with a join to see if that would work better, but I’m
> too frazzled to figure it out right now.

Regards,
-- 
Aristotle Pagaltzis // 

Re: [sqlite] sqlite puzzle

[A. Pagaltzis]

* A. Pagaltzis <[EMAIL PROTECTED]> [2006-05-03 00:30]:
> I tried to do it with a join to see if that would work better,
> but I’m too frazzled to figure it out right now.

I must be more frazzled than I thought.

SELECT
n1.name,
COUNT( n2.name ) rank
FROM names n1
CROSS JOIN names n2
WHERE n2.name < n1.name
GROUP BY n1.name
ORDER BY rank;

Regards,
-- 
Aristotle Pagaltzis // 

Re: [sqlite] sqlite puzzle

[A. Pagaltzis]

* JP <[EMAIL PROTECTED]> [2006-05-02 22:10]:
> SQLite provides a way to get the N-th row given a SQL statement, with 
> LIMIT 1 and OFFSET .
> 
> Can the reverse be done in an efficient way?  For example, given a table 
> with 1million names, how can I return the row number for a particular 
> element?  i.e. something like
> 
>  SELECT rownum FROM
>   (SELECT name, FROM clients
>   WHERE name='foo' ORDER BY name)
> 

Assuming your client names are unique, this should work:

SELECT
(
SELECT
COUNT(*)
FROM clients c2
WHERE c2.name < c1.name
ORDER BY c2.name
) rank,
c1.name
FROM clients c1
ORDER BY rank;

On MySQL5 and PostgreSQL, it works as intended. Unfortunately,
SQLite complains that it doesn’t know about `c1.name`. I tried to
do it with a join to see if that would work better, but I’m too
frazzled to figure it out right now.

Regards,
-- 
Aristotle Pagaltzis // 

Re: [sqlite] sqlite puzzle

[A. Pagaltzis]

* Kurt Welgehausen <[EMAIL PROTECTED]> [2006-05-02 22:15]:
> No, you can't do that in SQL.

You can.

> The results of an SQL query are a set of rows; the rows are not
> produced in any guaranteed order.

If this was true, how would `LIMIT` work?

Sure, the results do not have any implicit order, but you can
certainly explicitly define one using an `ORDER BY` clause.

Regards,
-- 
Aristotle Pagaltzis // 

Re: [sqlite] sqlite puzzle

[Jay Sprenkle]

On 5/2/06, JP <[EMAIL PROTECTED]> wrote:

Can the reverse be done in an efficient way?  For example, given a table
with 1million names, how can I return the row number for a particular
element?  i.e. something like


is
select rowid from table where name = 'foo'
what you want?

Re: [sqlite] sqlite puzzle

[Kurt Welgehausen]

JP <[EMAIL PROTECTED]> wrote:

> SQLite provides a way to get the N-th row given a SQL statement, with 
> LIMIT 1 and OFFSET .
>
> Can the reverse be done in an efficient way?  For example, given a table 
> with 1million names, how can I return the row number for a particular 
> element?

No, you can't do that in SQL.  The results of an SQL query
are a set of rows; the rows are not produced in any
guaranteed order.  The set is converted to an ordered list
only by a sort (order by) after all the result rows are
retrieved.

If I understand correctly what you want to do, you'll have
to retrieve all the rows into an ordered collection, then
search for the name you're interested in.  An alternative
would be to add a column RowNum and update it every time
you insert a row.

  newRowNum <- 1 + {select max(RowNum) from t where Name < 'foo'} or 0
  update t set RowNum = RowNum + 1 where RowNum >= newRowNum
  insert into t (Name, RowNum, ...) values ('foo', newRowNum, ...)

Regards