Re: [sqlite] sql function to change multiple links within a DB

[teahou]

Simon Slavin-3 wrote:
> 
> 
> On 1 Sep 2011, at 12:37am, teahou wrote:
> 
>> The closest I have come is to do it in parts, I came up with this:
>> 
>> UPDATE questions SET answer = REPLACE(answer, '   style="text-decoration:
>> underline; color: blue"
>> onclick="Ti.App.fireEvent(''openContent'', {kind: ''FAQ'', find: "' );
>> 
>> As you can see, this just does the first part, and forces me to do each
>> category individually.  
>> 
>> I had thought to use a combination of substr/instr but the link locations
>> vary, it could be at the beginning of the text, or it could start after
>> 100
>> characters.
> 
> Do it in software.   That's what programming languages are for.  10
> seconds to take a backup copy of the database.  30 minutes to write the
> program and test it, 10 seconds to run it.
> 
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 


I don't know any.  Well, i know some VB but i wouldn't even know where to
begin.  I guess I will look into it unless there are some other replys

-- 
View this message in context: 
http://old.nabble.com/sql-function-to-change-multiple-links-within-a-DB-tp32376393p32376446.html
Sent from the SQLite mailing list archive at Nabble.com.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sql function to change multiple links within a DB

[Simon Slavin]

On 1 Sep 2011, at 12:37am, teahou wrote:

> The closest I have come is to do it in parts, I came up with this:
> 
> UPDATE questions SET answer = REPLACE(answer, ' style="text-decoration: underline; color: blue"
> onclick="Ti.App.fireEvent(''openContent'', {kind: ''FAQ'', find: "' );
> 
> As you can see, this just does the first part, and forces me to do each
> category individually.  
> 
> I had thought to use a combination of substr/instr but the link locations
> vary, it could be at the beginning of the text, or it could start after 100
> characters.

Do it in software.   That's what programming languages are for.  10 seconds to 
take a backup copy of the database.  30 minutes to write the program and test 
it, 10 seconds to run it.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] sql function to change multiple links within a DB

[teahou]

Hello.  I am doing some work on a SQLite table with just over 5000 rows.  One
column holds some html text with links within, and I need to change every
one of  those links to a new format.

A sample link looks like this:

/FAQ/Doctors Doctors 

We will call the 'FAQ' portion of the link, the category and the 'Doctors'
portion the Item.

I need to change it this:

Doctors 

I would like change all links with one go, but I can't figure out a way to
'wildcard' the category and items.  There are 60 different categories and
thousands of different items.

The closest I have come is to do it in parts, I came up with this:

UPDATE questions SET answer = REPLACE(answer, 'http://old.nabble.com/sql-function-to-change-multiple-links-within-a-DB-tp32376393p32376393.html
Sent from the SQLite mailing list archive at Nabble.com.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] (no subject)

[Igor Tandetnik]

On 8/31/2011 5:56 PM, Tim Streater wrote:

In the above, each database is newly created as shown. What I had
forgotten to do was to create the "test" table in the second database
before copying the data. What seems to happen is that, lacking a
"test" table in the test2 database, SQLite appears to assume that I
must mean the "test" table in the test1 database - it tries to copy
data from the table into itself and so gets the error above.


Yes. This is documented behavior - see http://sqlite.org/lang_attach.html.


Is this reasonable behaviour? I might have expected to have a "no such table" 
error.


Which part of the documentation might have led you to expect that?
--
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] (no subject)

[Jay A. Kreibich]

On Wed, Aug 31, 2011 at 10:56:00PM +0100, Tim Streater scratched on the wall:

> In the above, each database is newly created as shown. What I had
> forgotten to do was to create the "test" table in the second
> database before copying the data. What seems to happen is that,
> lacking a "test" table in the test2 database, SQLite appears to
> assume that I must mean the "test" table in the test1 database -
> it tries to copy data from the table into itself and so gets the
> error above.
> 
> Is this reasonable behaviour? I might have expected to have a
> "no such table" error.

  Reasonable or not, it is the defined behavior:

http://sqlite.org/lang_attach.html

Tables in an attached database can be referred to using the syntax
database-name.table-name. If the name of the table is unique across
all attached databases and the main and temp databases, then the
database-name prefix is not required. If two or more tables in
different databases have the same name and the database-name prefix
is not used on a table reference, then the table chosen is the one
in the database that was least recently attached.

  In other words, SQLite will generally search the temp database, the
  main database, and then all attached databases in index order.  This
  brings up some odd edge cases, as the temp database is searched
  before the main database, even though the main database has a lower
  index, but it generally works as expected.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] (no subject)

[Tim Streater]

Today when trying to copy from one database to another, I had the following 
error (simplified example below):

  Second-Mini% sqlite3 test1
  sqlite> create table test (absid integer primary key, otherfield integer);
  sqlite> insert into test (absid,otherfield) values (null, 10);
  sqlite> insert into test (absid,otherfield) values (null, 20);
  sqlite> select * from test;
  absid | otherfield
  --+---
  1 | 10
  2 | 20
  sqlite> ^D

  Second-Mini% sqlite3 test2
  sqlite> attach database test1 as src;
  sqlite> insert into test select * from src.test;
  Error: PRIMARY KEY must be unique
  sqlite> 

In the above, each database is newly created as shown. What I had forgotten to 
do was to create the "test" table in the second database before copying the 
data. What seems to happen is that, lacking a "test" table in the test2 
database, SQLite appears to assume that I must mean the "test" table in the 
test1 database - it tries to copy data from the table into itself and so gets 
the error above.

Is this reasonable behaviour? I might have expected to have a "no such table" 
error.

--
Cheers  --  Tim
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] max() with LIMIT

[Kees Nuyt]

On Wed, 31 Aug 2011 08:12:10 -0400, "Igor Tandetnik"
 wrote:

>Ivan Shmakov  wrote:
>>> Tobias Vesterlund writes:
>>> Is it possible to get the highest value in a "limited column" when
>>> using LIMIT?
>> 
>> Sure.
>> 
>> SELECT max (id) FROM (SELECT id FROM t WHERE id > 0 LIMIT 10);
>
> This only works by accident. There's no requirement
> that the subselect return rows in any particular order.
> It can, in principle, choose any ten rows.

You're right. I should have taken that into consideration in my
posting in this thread.

-- 
  (  Kees Nuyt
  )
c[_]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] System.Data.SQLite Release

[Joe Mistachkin]

> 
> Just wondering when the next release of System.Data.SQLite will be
> available.  
> 

My current plan is to produce a release before the 15th of September.

--
Joe Mistachkin

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] max() with LIMIT

[Ivan Shmakov]

> Igor Tandetnik writes:
> Ivan Shmakov  wrote:
> Tobias Vesterlund writes:

 >>> Is it possible to get the highest value in a "limited column" when
 >>> using LIMIT?

 >> Sure.

 >> SELECT max (id) FROM (SELECT id FROM t WHERE id > 0 LIMIT 10);

 > This only works by accident.  There's no requirement that the
 > subselect return rows in any particular order.  It can, in principle,
 > choose any ten rows.

There wasn't the requirement that the query should be
unambiguous in its interpretation in the OP, either.

However, yes, almost anytime LIMIT is used, it has to be
complemented with ORDER BY for a definite result.

-- 
FSF associate member #7257  Coming soon: Software Freedom Day
http://mail.sf-day.org/lists/listinfo/ planning-ru (ru), sfd-discuss (en)

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] System.Data.SQLite Release

[Patrick Earl]

Just wondering when the next release of System.Data.SQLite will be
available.  There's a bug, that was already reported and fixed, in the
current release that badly breaks NHibernate / ActiveRecord.

Patrick Earl
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to configure any parameter to set max number of records to be added in SQLite DB

[Richard Hipp]

On Tue, Aug 30, 2011 at 11:55 PM, Tarun  wrote:

> Hi All,
>
> Is there any configurable parameter which allows us to set maximum
> number of records that is allowed to be inserted. Once this limit is
> reached, insert SQL query should fail inserting records telling max
> records have been inserted in DB table.
>
> Please let me know. Waiting for response.
>

Are you concerned about hostile user agents soaking up too much disk space?

We have a solution for that: the test_quota.c VFS shim.  See
http://www.sqlite.org/src/artifact/a391c866217e92?ln=13-28 for some sketchy
documentation.  Firefox uses this to limit the amount of disk space that an
HTML5 web application can use with IndexedDB, as one of many defenses
against denial-of-service attacks.



>
> Thank you all.
>
> --
> Regards,
> - Tarun Thakur
> Module Lead
> NEC HCL System Technologies, Noida
> www.nechclst.in
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Clarification about Triggers

[Black, Michael (IS)]

Not accusing you of being pompous/patronizing at all...just having some fun 
with it...



I prefer the Pluralis Majestatis interpretation myself:-)

http://en.wikipedia.org/wiki/We



Shall we agree?



Michael D. Black

Senior Scientist

NG Information Systems

Advanced Analytics Directorate




From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Dan Kennedy [danielk1...@gmail.com]
Sent: Wednesday, August 31, 2011 8:57 AM
To: sqlite-users@sqlite.org
Subject: EXT :Re: [sqlite] Clarification about Triggers

On 08/31/2011 06:34 PM, Black, Michael (IS) wrote:
> Doohyes "we" missed that.  But shouldn't new.rowid be undefined then 
> rather than return -1?  Much like old.rowid is undefined?  That might have 
> helped "us" in recognizing "our" mistake.

Fair enough. Sounded pompous. I say "we" because I only realized
what was happening after trying to debug the script as if it were
an SQLite bug in AFTER triggers.

> The docs say
> The value of NEW.rowid is undefined in a BEFORE INSERT trigger in which the 
> rowid is not explicitly set to an integer.
> http://www.sqlite.org/lang_createtrigger.html
>
> And...shouldn't "after" or "before" or "instead" be mandatory?  The docs 
> don't declare a default condition either.

I think it's an SQL thing. BEFORE is the default. SQLite
docs don't say that though.
___
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

Re: [sqlite] Clarification about Triggers

[Dan Kennedy]

On 08/31/2011 06:34 PM, Black, Michael (IS) wrote:

Doohyes "we" missed that.  But shouldn't new.rowid be undefined then rather than return -1?  
Much like old.rowid is undefined?  That might have helped "us" in recognizing "our" 
mistake.


Fair enough. Sounded pompous. I say "we" because I only realized
what was happening after trying to debug the script as if it were
an SQLite bug in AFTER triggers.


The docs say
The value of NEW.rowid is undefined in a BEFORE INSERT trigger in which the 
rowid is not explicitly set to an integer.
http://www.sqlite.org/lang_createtrigger.html

And...shouldn't "after" or "before" or "instead" be mandatory?  The docs don't 
declare a default condition either.


I think it's an SQL thing. BEFORE is the default. SQLite
docs don't say that though.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite3 linking error on 64 bit linux machine

[Tarun]

Hi Igor,

Thanks for reply.

This issue is resolved. I downloaded source code sqlite-autoconf-3070701.tar.gz

and built on 64 bit machine. It built successfully and I am able to
link with it.

Thanks and Regards,
Tarun


On 8/31/11, Igor Tandetnik  wrote:
> Tarun  wrote:
>> I am trying to use SQLite3 library on 64 bit machine. I have copied
>> libsqlite3.so from /usr/lib/ on 32bit linux machine to 64bit linux
>> machine.
>
> How do you expect this to work? The library from 32-bit machine contains
> 32-bit code. You can't link that with your 64-bit program.
>
> You'll likely have to build 64-bit SQLite library from sources.
> --
> Igor Tandetnik
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>


-- 
Regards,
- Tarun Thakur
Module Lead
NEC HCL System Technologies, Noida
www.nechclst.in
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] EXT : SQLite3 linking error on 64 bit linux machine

[Black, Michael (IS)]

Just download the amalgamation and include sqlite3.c and sqlite3.h in your 
project.

Much easier than trying to mess with shared libraries which you probably don't 
need anyways.

That would be the zip file on here:

http://www.sqlite.org/download.html







Michael D. Black

Senior Scientist

NG Information Systems

Advanced Analytics Directorate




From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Tarun [er.tarun.9...@gmail.com]
Sent: Wednesday, August 31, 2011 7:47 AM
To: sqlite-users@sqlite.org
Subject: EXT :[sqlite] SQLite3 linking error on 64 bit linux machine

Hi All,

I am trying to use SQLite3 library on 64 bit machine. I have copied
libsqlite3.so from /usr/lib/ on 32bit linux machine to 64bit linux
machine.

While program compilation on 64bit machine, I am getting linking error
given below:
/usr/lib64/gcc/x86_64-suse-linux/4.3/../../../../x86_64-suse-linux/bin/ld:
skipping incompatible /usr/lib/libsqlite3.so when searching for
-lsqlite3
/usr/lib64/gcc/x86_64-suse-linux/4.3/../../../../x86_64-suse-linux/bin/ld:
cannot find -lsqlite3

Please let me know what I should do to get it resolved.

Thanks in advance.

--
Regards,
- Tarun Thakur
Module Lead
NEC HCL System Technologies, Noida
www.nechclst.in
___
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

Re: [sqlite] max() with LIMIT

[Simon Slavin]

On 31 Aug 2011, at 8:38am, Tobias Vesterlund wrote:

> Is it possible to get the highest value in a "limited column" when using 
> LIMIT?

Sort by that value.  For instance,

SELECT id FROM t ORDER BY id DESC LIMIT 1

will give you the biggest value of 'id' that can be found.  It's not needed in 
this case, but if you sort using a different field, make sure there's an index 
which allows quick sorting on that field.  It doesn't matter whether the index 
specifies that field as ASC or DESC.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite3 linking error on 64 bit linux machine

[Igor Tandetnik]

Tarun  wrote:
> I am trying to use SQLite3 library on 64 bit machine. I have copied
> libsqlite3.so from /usr/lib/ on 32bit linux machine to 64bit linux
> machine.

How do you expect this to work? The library from 32-bit machine contains 32-bit 
code. You can't link that with your 64-bit program.

You'll likely have to build 64-bit SQLite library from sources.
-- 
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLite3 linking error on 64 bit linux machine

[Tarun]

Hi All,

I am trying to use SQLite3 library on 64 bit machine. I have copied
libsqlite3.so from /usr/lib/ on 32bit linux machine to 64bit linux
machine.

While program compilation on 64bit machine, I am getting linking error
given below:
/usr/lib64/gcc/x86_64-suse-linux/4.3/../../../../x86_64-suse-linux/bin/ld:
skipping incompatible /usr/lib/libsqlite3.so when searching for
-lsqlite3
/usr/lib64/gcc/x86_64-suse-linux/4.3/../../../../x86_64-suse-linux/bin/ld:
cannot find -lsqlite3

Please let me know what I should do to get it resolved.

Thanks in advance.

-- 
Regards,
- Tarun Thakur
Module Lead
NEC HCL System Technologies, Noida
www.nechclst.in
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] max() with LIMIT

[Igor Tandetnik]

Tobias Vesterlund  wrote:
> But If I do SELECT max(id) FROM t WHERE id > 0 LIMIT 10; it will return 99.
> 
> My logic, which may be flawed in this case, tells me the third SELECT should 
> return 10 and not 99.
> 
> Is it possible to get the highest value in a "limited column" when using 
> LIMIT?

You seem to want the tenth smallest ID. Try this:

select id from t where id > 0 order by id limit 1 offset 9;

-- 
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] max() with LIMIT

[Igor Tandetnik]

Ivan Shmakov  wrote:
>> Tobias Vesterlund writes:
>> Is it possible to get the highest value in a "limited column" when
>> using LIMIT?
> 
> Sure.
> 
> SELECT max (id) FROM (SELECT id FROM t WHERE id > 0 LIMIT 10);

This only works by accident. There's no requirement that the subselect return 
rows in any particular order. It can, in principle, choose any ten rows.
-- 
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Clarification about Triggers

[Black, Michael (IS)]

Doohyes "we" missed that.  But shouldn't new.rowid be undefined then rather 
than return -1?  Much like old.rowid is undefined?  That might have helped "us" 
in recognizing "our" mistake.

The docs say
The value of NEW.rowid is undefined in a BEFORE INSERT trigger in which the 
rowid is not explicitly set to an integer.
http://www.sqlite.org/lang_createtrigger.html

And...shouldn't "after" or "before" or "instead" be mandatory?  The docs don't 
declare a default condition either.




Michael D. Black
Senior Scientist
NG Information Systems
Advanced Analytics Directorate




From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Dan Kennedy [danielk1...@gmail.com]
Sent: Wednesday, August 31, 2011 1:19 AM
To: sqlite-users@sqlite.org
Subject: EXT :Re: [sqlite] Clarification about Triggers


On 08/30/2011 10:48 PM, Black, Michael (IS) wrote:
> I found that if you used the default rowid it always gave -1 for the value.
> That's why I put in it's own key.
>
> I don't understand why this doesn't work...perhaps somebody can point out the 
> error here...new.rowid contains -1.  I would think that rowid ought to be 
> available after the insert.
> This is using 3.7.4
>
> create table temp_01(val float);
> create table temp_02(val float);
> create table total(val float);
> create table row(row2 integer);
> create trigger after insert on temp_01
> begin
> insert into total values((select new.val+temp_02.val from temp_02 where 
> temp_02.rowid=new.rowid));
> insert into row values(new.rowid);
> end;

The error is that we are mistaking the above for an AFTER
trigger. It is not. It is a BEFORE trigger named "after".
And the value of new.rowid is not defined in a BEFORE
trigger.

Rewrite as:

   CREATE TRIGGER my_new_trigger AFTER INSERT ...

and it should work.

Dan.
___
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

Re: [sqlite] max() with LIMIT

[Kees Nuyt]

On Wed, 31 Aug 2011 09:38:46 +0200, Tobias Vesterlund
 wrote:

> Hi,
>
> I'm want to get the max value out of a certain column in a table.
>
> Table t has a column named id, which ranges from 0 to 99.
>
> If I do SELECT max(id) FROM t;
>   it will return 99.
> If I do SELECT id FROM t WHERE id > 0 LIMIT 10;
>   it will return 1,2,3,4,5,6,7,8,9,10
> But If I do SELECT max(id) FROM t WHERE id > 0 LIMIT 10;
>   it will return 99.
>
> My logic, which may be flawed in this case,
> tells me the third SELECT should return 10 and not 99.

http://www.sqlite.org/lang_select.html tells:

The LIMIT clause is used to place an upper bound on the number of
rows returned by a SELECT statement. [...]
Otherwise, the SELECT returns the first N rows of its result set
only, where N is the value that the LIMIT expression evaluates to.
Or, if the SELECT statement would return less than N rows without
a LIMIT clause, then the entire result set is returned. 

Your statement SELECT max(id) FROM t WHERE id > 0 always returns
exactly one row, so LIMIT has no effect here.

> Is it possible to get the highest value in a "limited column" when using 
> LIMIT?

It is unclear to me what you mean with limited column.
Perhaps this statement is what you are looking for?

 SELECT max(id) FROM (
SELECT id FROM t 
 WHERE id > 0
 LIMIT 10);
-- 
  (  Kees Nuyt
  )
c[_]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] max() with LIMIT

[Tobias Vesterlund]

Right you are, thank you! 

Regards,
Tobias 

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Ivan Shmakov
Sent: den 31 augusti 2011 10:13
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] max() with LIMIT

> Tobias Vesterlund writes:

[...]

 > If I do SELECT max(id) FROM t; it will return 99.

 > If I do SELECT id FROM t WHERE id > 0 LIMIT 10; it will return  > 
 > 1,2,3,4,5,6,7,8,9,10

 > But If I do SELECT max(id) FROM t WHERE id > 0 LIMIT 10; it will  > return 
 > 99.

 > My logic, which may be flawed in this case, tells me the third SELECT  > 
 > should return 10 and not 99.

LIMIT applies /after/ the aggregation.  Consider, e. g., using
GROUP BY, and then selecting no more than 10 groups' maximums.

 > Is it possible to get the highest value in a "limited column" when  > using 
 > LIMIT?

Sure.

SELECT max (id) FROM (SELECT id FROM t WHERE id > 0 LIMIT 10);

-- 
FSF associate member #7257  Coming soon: Software Freedom Day
http://mail.sf-day.org/lists/listinfo/ planning-ru (ru), sfd-discuss (en)

___
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

Re: [sqlite] max() with LIMIT

[Ivan Shmakov]

> Tobias Vesterlund writes:

[…]

 > If I do SELECT max(id) FROM t; it will return 99.

 > If I do SELECT id FROM t WHERE id > 0 LIMIT 10; it will return
 > 1,2,3,4,5,6,7,8,9,10

 > But If I do SELECT max(id) FROM t WHERE id > 0 LIMIT 10; it will
 > return 99.

 > My logic, which may be flawed in this case, tells me the third SELECT
 > should return 10 and not 99.

LIMIT applies /after/ the aggregation.  Consider, e. g., using
GROUP BY, and then selecting no more than 10 groups' maximums.

 > Is it possible to get the highest value in a "limited column" when
 > using LIMIT?

Sure.

SELECT max (id) FROM (SELECT id FROM t WHERE id > 0 LIMIT 10);

-- 
FSF associate member #7257  Coming soon: Software Freedom Day
http://mail.sf-day.org/lists/listinfo/ planning-ru (ru), sfd-discuss (en)

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] max() with LIMIT

[Tobias Vesterlund]

Hi,

I'm want to get the max value out of a certain column in a table.

Table t has a column named id, which ranges from 0 to 99.

If I do SELECT max(id) FROM t; it will return 99.
If I do SELECT id FROM t WHERE id > 0 LIMIT 10; it will return 
1,2,3,4,5,6,7,8,9,10
But If I do SELECT max(id) FROM t WHERE id > 0 LIMIT 10; it will return 99.

My logic, which may be flawed in this case, tells me the third SELECT should 
return 10 and not 99.

Is it possible to get the highest value in a "limited column" when using LIMIT?

Regards,
Tobias

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users