Re: [sqlite] What is quicker?
Dennis Cote wrote: > John Stanton wrote: >> But for practical arithmetic probability or possibility is not close >> enough. It must be certainty. > > There is a possibility that your code could be asked to compare two > equal floating point numbers. To be correct, it must handle that case. > If it does not, it is certainly broken. > > While you must be careful with floating point numbers, statements such > as yours: "The point about using floating point is that there is no > equal, only less or greater, because it is an approximation." just add > confusion to the issue. There is very definitely an "equal" when dealing > with floating point numbers, and it has nothing to do with floating > point format sometimes being an approximation. > > >> I make the point because it has been my >> observation over the years that some of the silliest and most >> embarrassing simple IT errors have been caused by the inappropriate >> usage of floating point numbers. > > That is true, but neither this or your first post was applicable to the > correction that Steve suggested (and Richard accepted). > > In fact the equal case, when xmin and xmax are set to the same value is > required to allow points (rectangles with zero area and zero length) to > be handled by the RTree module. It would be incorrect to say that xmin > must be less than xmax when they can be equal. > > Dennis Cote > I was not interested in being pedantic, just in offering some practical rule of thumb advice. > _ > 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] What is quicker?
In relation with the floating point number and its IEEE internal representation, may be of interest: http://babbage.cs.qc.edu/IEEE-754/ HTH Adolfo ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] What is quicker?
Hence the word "SOME"... :) Asif Lodhi <[EMAIL PROTECTED]> wrote: Hi Ken, On 6/6/08, Ken wrote: > Some numbers can be represented exactly using the > floating point type. . Here is a reference from "The C++ Programming Language, 3rd Edition" by Bjarne Stroustrup, Page 835, section - C.6.2.6: int i = float ( 1234567890); left i with the value 1234567936 on a machine, where both ints and floats are represented using 32 bits. Clearly, it is best to avoid using potentially value-destroying implicit conversions. . - -- Asif ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] What is quicker?
Hi Ken, On 6/6/08, Ken <[EMAIL PROTECTED]> wrote: > Some numbers can be represented exactly using the > floating point type. . Here is a reference from "The C++ Programming Language, 3rd Edition" by Bjarne Stroustrup, Page 835, section - C.6.2.6: int i = float ( 1234567890); left i with the value 1234567936 on a machine, where both ints and floats are represented using 32 bits. Clearly, it is best to avoid using potentially value-destroying implicit conversions. . - -- Asif ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] What is quicker?
John Stanton wrote: > > But for practical arithmetic probability or possibility is not close > enough. It must be certainty. There is a possibility that your code could be asked to compare two equal floating point numbers. To be correct, it must handle that case. If it does not, it is certainly broken. While you must be careful with floating point numbers, statements such as yours: "The point about using floating point is that there is no equal, only less or greater, because it is an approximation." just add confusion to the issue. There is very definitely an "equal" when dealing with floating point numbers, and it has nothing to do with floating point format sometimes being an approximation. > I make the point because it has been my > observation over the years that some of the silliest and most > embarrassing simple IT errors have been caused by the inappropriate > usage of floating point numbers. That is true, but neither this or your first post was applicable to the correction that Steve suggested (and Richard accepted). In fact the equal case, when xmin and xmax are set to the same value is required to allow points (rectangles with zero area and zero length) to be handled by the RTree module. It would be incorrect to say that xmin must be less than xmax when they can be equal. Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] What is quicker?
One of the things that people fail to understand is that floating point
numbers are stored in *binary*. In fact, I bet a number of people who
understand the exact binary formatting of integers don't understand that the
technique translates pretty much directly into floating point: a floating
point number is recorded like 1.0111001010101110101
So when they hear "floating point numbers are inaccurate because of rounding
errors", they often think "Oh, that doesn't apply to me -- I'm not doing
anything with enough decimal places to hit a rounding error." This exact
sort of problem cropped up in a financial transaction processing system I
helped maintain.
Problem: Sometimes, members's transactions would be declined even though
they had enough to cover the transaction.
This was narrowed down to
Specific problem: Member X is unable to make a purchase for $1.40 cents
despite them having 40 cents in their account and a $1.00-off coupon.
I looked at the code in question and announced it was a rounding error due
to the use of a 'double' for storing currency values. "How is that
possible? It's only two decimal places, I've seen these things work for half
a dozen decimal places!" I was asked.
So I demonstrated what happened: The application's test was (total amount)
- (balance) <= (coupon). Or something like that. It was years ago; all I
remember is that the three numbers were $1.40, $1.00, and $0.40. So I
translated everything into the internal binary form:
Purchase amount: $1.40 =>
1.0110011001100110011001100110011001100110011001100110 * (2**0)
Member's balance: $0.40 =>
1.1001100110011001100110011001100110011001100110011010 * (2**-2)
To add these together, they need to be adjusted to the same exponent:
Purchase amount: $1.40 =>
1.0110011001100110011001100110011001100110011001100110 * (2**0)
Member's balance: $0.40 =>
0.011001100110011001100110011001100110011001100110011010
* (2**0)
This is where things go wrong. You see that extra '10' at the end of the
member's balance? The floating point process doesn't have room for it, so
it rounds. And much the same way as 0.5 rounds up to 1.0, so does binary
0.1:
Purchase amount: $1.40 =>
1.0110011001100110011001100110011001100110011001100110 * (2**0)
Member's balance: $0.40 =>
0.0110011001100110011001100110011001100110011001100111
* (2**0)
Now we subtract:
1.0110011001100110011001100110011001100110011001100110
- 0.0110011001100110011001100110011001100110011001100111
0.
This is *practically* 1, in much the same way as
0.99 is *practically* 1. But it's still
technically less than 1. So when the application compared it to the coupon
amount, or whichever it was, the rounding error caused a false failure and
the transaction was declined.
Things are easier to understand if you realize that for any fraction (P/Q),
if Q is not exactly a power of 2, then the answer cannot be exactly
represented in binary. In contrast, for our decimal system, any fraction
(P/Q) cannot be represented exactly unless Q can be expressed as (some power
of 2)*(some power of 5).
For your edification, I wrote a Perl script to tell how many Qs offer exact
representations in bases 2, 10, and 60. These are the results:
Bin: 25 of 16777216 (0.00015%)
Decimal: 143 of 16777216 (0.00085%)
Base60: 836 of 16777216 (0.00498%)
This roughly indicates that if you have a number that can be expressed
exactly in decimal, there's only about a 1-in-6 chance that it's *also*
expressible exactly in binary without running into rounding errors. I also
threw in base 60 for comparison -- an arbitrary number is nearly 6 times as
likely to be expressible exactly using base-60 than it is in base-10. GPS
coordinates are expressed using base-60 (degrees, minutes, seconds).
== script ==
#!/usr/bin/perl
my ($b,$d,$b60) = (0,0,0);
my $max = 16_777_216;
for (1..$max) {
my $q = $_;
while ( ($q % 2) == 0 ) { $q /= 2; }
if ($q == 1) { $b++; }
while ( ($q % 5) == 0) { $q /= 5; }
if ($q == 1) { $d++; }
while ( ($q % 3) == 0) { $q /= 3; }
if ($q == 1) { $b60++; }
}
printf "%s: %d of $max (%.5f%%)\n", @$_, 100*$_->[1]/$max
for [Bin=>$b],[Decimal=>$d],[Base60=>$b60];
#!/usr/bin/perl
my ($b,$d,$b60) = (0,0,0);
my $max = 16_777_216;
for (1..$max) {
my $q = $_;
while ( ($q % 2) == 0 ) { $q /= 2; }
if ($q == 1) { $b++; }
while ( ($q % 5) == 0) { $q /= 5; }
if ($q == 1) { $d++; }
while ( ($q % 3) == 0) { $q /= 3; }
if ($q == 1) { $b60++; }
}
printf "%s: %d of $max (%.5f%%)\n", @$_, 100*$_->[1]/$max
for [Bin=>$b],[Decimal=>$d],[Base60=>$b60];
== snip ==
--
-- Stevie-O
Real programmers use COPY CON PROGRAM.EXE
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] What is quicker?
Some numbers can be represented exactly using the floating point type. According to ieee 754 spec: "All integers that are a power of 2 can be stored in a 32 bit float without rounding Precision decreases exponentially as the exponent increases" So for those numbers equality is necessary. John Stanton <[EMAIL PROTECTED]> wrote: Dennis Cote wrote: > John Stanton wrote: >> The point about using floating point is that there is no equal, only >> less or greater, because it is an approximation. If you want to use >> equality you must use some form of integer or fixed ppint numbers. >> > > That's not true at all. While it is not reliable to use equality tests > for different floating point numbers, there is very definitely the > possibility of two floating point numbers being equal. > > For any two floating point numbers with the same bit pattern, both < and > > will be false. All code using floating point numbers must be able to > handle the equal case correctly, it is usually just merged with one of > the < or > cases. > > That is what Steve's correction was. He suggested it should merge the > equal case with the < case. It usually makes no sense to merge the < and > > cases (this is simply another way to say not equal) as it was > originally documented. > > Dennis Cote But for practical arithmetic probability or possibility is not close enough. It must be certainty. I make the point because it has been my observation over the years that some of the silliest and most embarrassing simple IT errors have been caused by the inappropriate usage of floating point numbers. > ___ > 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 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] If SQLite Encryption Extension (SEE) FIPS 140-2 compliant?
On Fri, 6 Jun 2008 10:17:36 -0500, Dan wrote: > I see that SQLite Encryption Extension (SEE) uses AES > for its encryption algorithm. > Does this make SEE FIPS 140-2 compliant? This list should answer it. http://csrc.nist.gov/groups/STM/cmvp/documents/140-1/1401val2008.htm More general information: http://csrc.nist.gov/groups/STM/cmvp/validation.html >Thanks, Dan -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] What is quicker?
John Stanton wrote: > > The point about using floating point is that there is no equal, only > less or greater, because it is an approximation. If you want to use > equality you must use some form of integer or fixed ppint numbers. > That's not true at all. While it is not reliable to use equality tests for different floating point numbers, there is very definitely the possibility of two floating point numbers being equal. For any two floating point numbers with the same bit pattern, both < and > will be false. All code using floating point numbers must be able to handle the equal case correctly, it is usually just merged with one of the < or > cases. That is what Steve's correction was. He suggested it should merge the equal case with the < case. It usually makes no sense to merge the < and > cases (this is simply another way to say not equal) as it was originally documented. Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] What is quicker?
Steve Kallenborn wrote: > D. Richard Hipp wrote: >> On Jun 4, 2008, at 7:13 AM, Derrell Lipman wrote: >> >>> On Wed, Jun 4, 2008 at 10:01 AM, D. Richard Hipp <[EMAIL PROTECTED]> >>> wrote: >>> Let me strongly reiterate that you look into using the new R-Tree virtual table available for SQLite. R-Trees are specifically designed to do exactly the kind of query you are asking to do. See http://www.sqlite.org/cvstrac/fileview?f=sqlite/ext/rtree/README=1.2 >>> The README contains this text: >>> >>> CREATE VIRTUAL TABLE USING rtree() >>> >>>For example: >>> >>> CREATE VIRTUAL TABLE boxes USING rtree(boxno, xmin, xmax, ymin, >>> ymax); >>> CREATE VIRTUAL TABLE boxes USING rtree(1, 1.0, 3.0, 2.0, 4.0); >>> >>> Is that last line supposed to be >>> INSERT INTO boxes VALUES (1, 1.0, 3.0, 2.0, 4.0); >>> ? >> Yes. >> http://www.sqlite.org/cvstrac/fileview?f=sqlite/ext/rtree/README=1.3 >> >> D. Richard Hipp >> [EMAIL PROTECTED] >> >> >> >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> > In the same document: > "For each pair of real values, the first (leftmost) must be > less than or greater than the second." > > should presumable read "less than or equal to" > > Thanks >SteveK The point about using floating point is that there is no equal, only less or greater, because it is an approximation. If you want to use equality you must use some form of integer or fixed ppint numbers. > ___ > 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] how to save an information + Date in SQlite db?
Look at the built in Sqlite date and time functions. the_chill wrote: > Hello, how do I save a information + Date in a SQlite DB? I want later to > enter a date and get the Information. Like information from > 07.08.05-03.04.06 or so. I tryed SQlite browser but found no way. I need to > share the DB betwen a Linux and WinXP system wihtout a server. Any hints how > to orgenice the DB and the request? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] If SQLite Encryption Extension (SEE) FIPS 140-2 compliant?
I see that SQLite Encryption Extension (SEE) uses AES for its encryption algorithm. Does this make SEE FIPS 140-2 compliant? Thanks, Dan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Thank you
D. Richard Hipp a écrit : > I'd like to take credit for the new r-tree module because it is a fine > piece of work. But in truth the new r-tree module was written > entirely by Dan Kennedy. http://www.sqlite.org/crew.html Good job, > Dan! > > D. Richard Hipp > [EMAIL PROTECTED] > > > Thank you to Dan and Richard then, Very fine work indeed, thanks a lot, it complete the spatialite project quite nicely. Noël Frankinet ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Binding Date value in Prepare/bind
"Bharath Booshan L" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > We have developed an App that was written for Version 3.1.3 available > in > Mac OS 10.4. > Now I have to move all the sqlite_exec to prepare/step/finalize > methods, > but as per the documents, it says 'use of sqlite_prepare is not > recommended' Well, it's not recommended for applications using SQLite version where sqlite_prepare_v2 is available. If it's not available, you don't have much choice. > Is sqlite_prepare supported in 3.1.3? Yes. It's supported in all 3.* versions. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Thank you
Kudos!!! to Dan/DRH. We always get responses from Dan/DRH which is logical,meaningful and on dot. regards ragha ** This email and its attachments contain confidential information from HUAWEI, which is intended only for the person or entity whose address is listed above. Any use of the information contained herein in any way (including, but not limited to, total or partial disclosure, reproduction, or dissemination) by persons other than the intended recipient(s) is prohibited. If you receive this e-mail in error, please notify the sender by phone or email immediately and delete it! * - Original Message - From: "D. Richard Hipp" <[EMAIL PROTECTED]> Date: Friday, June 6, 2008 6:02 pm Subject: Re: [sqlite] Thank you > I'd like to take credit for the new r-tree module because it is a > fine > piece of work. But in truth the new r-tree module was written > entirely by Dan Kennedy. http://www.sqlite.org/crew.html Good > job, > Dan! > > D. Richard Hipp > [EMAIL PROTECTED] > > > > ___ > 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] Thank you
I'd like to take credit for the new r-tree module because it is a fine piece of work. But in truth the new r-tree module was written entirely by Dan Kennedy. http://www.sqlite.org/crew.html Good job, Dan! D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Binding Date value in Prepare/bind
Thanks Igor,
>> I am asking this because julianday(date('1984-03-03')) =
>> julianday('1984-03-03'). Right?
>
> Right. In fact, date('1984-03-03') is a no-op: the result of
> date('1984-03-03') is simply '1984-03-03'. Though I fail to see how this
> fact is relevant to your original question.
I had a doubt that date('1984-03-03') might take some reasonable amount of
time, where I am using this date funtion just for the sake of date('now').
I did not know julianday('now') is supported.
One more question,
We have developed an App that was written for Version 3.1.3 available in
Mac OS 10.4.
Now I have to move all the sqlite_exec to prepare/step/finalize methods,
but as per the documents, it says 'use of sqlite_prepare is not recommended'
Is sqlite_prepare supported in 3.1.3? If not, I seems there is no other
option than to move to 3.3.9 & above.
Thanks for you valuable time,
Bharath
On 6/6/08 5:33 PM, "Igor Tandetnik" <[EMAIL PROTECTED]> wrote:
> "Bharath Booshan L"
> <[EMAIL PROTECTED]> wrote in
> message news:[EMAIL PROTECTED]
>> 1. Can I use sqlite3_prepare_v2 in Version 3.1.3?
>
> No. It was introduced in v3.3.9
>
>> 2. How do I bind date values using prepare/bind methods?
>
> SQLite doesn't have dedicated date or time types. You may choose to
> store timestamps as strings, as julian dates (floating point numbers) or
> as Unix epoch timestamps (integers). See also
>
> http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions
>
>> Eg: INSERT INTO TABLE Info(Name,DOB)
>> values('XYZ',julianday('1984-03-03'));
>>
>> For above example I can write a prepared statement as
>>
>> INSERT INTO TABLE Info(Name,DOB) values(?,julianday(?))
>
> Here, you are replacing two string literals with parameter placeholders.
> So you bind them as strings.
>
>> But how do I write prepared statement if I want to insert date('now')
>> value into the table, like below query
>>
>> INSERT INTO TABLE Info(Name,DOB) values('XYZ',julianday(date('now'))
>
> julianday('now') would work just as well. So you can use your first
> statement, and bind 'now' for the parameter.
>
>> I am asking this because julianday(date('1984-03-03')) =
>> julianday('1984-03-03'). Right?
>
> Right. In fact, date('1984-03-03') is a no-op: the result of
> date('1984-03-03') is simply '1984-03-03'. Though I fail to see how this
> fact is relevant to your original question.
>
> Igor Tandetnik
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
---
Robosoft Technologies - Come home to Technology
Disclaimer: This email may contain confidential material. If you were not an
intended recipient, please notify the sender and delete all copies. Emails to
and from our network may be logged and monitored. This email and its
attachments are scanned for virus by our scanners and are believed to be safe.
However, no warranty is given that this email is free of malicious content or
virus.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Binding Date value in Prepare/bind
"Bharath Booshan L"
<[EMAIL PROTECTED]> wrote in
message news:[EMAIL PROTECTED]
> 1. Can I use sqlite3_prepare_v2 in Version 3.1.3?
No. It was introduced in v3.3.9
> 2. How do I bind date values using prepare/bind methods?
SQLite doesn't have dedicated date or time types. You may choose to
store timestamps as strings, as julian dates (floating point numbers) or
as Unix epoch timestamps (integers). See also
http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions
> Eg: INSERT INTO TABLE Info(Name,DOB)
> values('XYZ',julianday('1984-03-03'));
>
> For above example I can write a prepared statement as
>
> INSERT INTO TABLE Info(Name,DOB) values(?,julianday(?))
Here, you are replacing two string literals with parameter placeholders.
So you bind them as strings.
> But how do I write prepared statement if I want to insert date('now')
> value into the table, like below query
>
> INSERT INTO TABLE Info(Name,DOB) values('XYZ',julianday(date('now'))
julianday('now') would work just as well. So you can use your first
statement, and bind 'now' for the parameter.
> I am asking this because julianday(date('1984-03-03')) =
> julianday('1984-03-03'). Right?
Right. In fact, date('1984-03-03') is a no-op: the result of
date('1984-03-03') is simply '1984-03-03'. Though I fail to see how this
fact is relevant to your original question.
Igor Tandetnik
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Thank you
I would like to thank all the participatns of this list for the very useful information i got here the last days. A big thank you to everyone, including of course Mr Hipp. The rtree implementation is really quick and does work like a charm. Best regards, -- Christophe Leske www.multimedial.de - [EMAIL PROTECTED] http://www.linkedin.com/in/multimedial Lessingstr. 5 - 40227 Duesseldorf - Germany 0211 261 32 12 - 0177 249 70 31 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL question
Hi Simon, it works very well. Now I study it and I try to understand why it works :-) Thanks a lot Andrea Simon Davies ha scritto: > Andrea, > > This appears to do what you want... > > SQLite version 3.4.2 > Enter ".help" for instructions > sqlite> > sqlite> create table tst( name text, score integer, info text ); > sqlite> insert into tst values( 'A', 289, 'A1' ); > sqlite> insert into tst values( 'C', 29, 'C1' ); > sqlite> insert into tst values( 'A', 29, 'A2' ); > sqlite> insert into tst values( 'C', 129, 'C2' ); > sqlite> insert into tst values( 'C', 19, 'C3' ); > sqlite> insert into tst values( 'A', 1129, 'A3' ); > sqlite> insert into tst values( 'B', 19, 'B1' ); > sqlite> insert into tst values( 'A', 19, 'A4' ); > sqlite> insert into tst values( 'B', 9, 'B2' ); > sqlite> insert into tst values( 'B', 99, 'B3' ); > sqlite> > sqlite> select * fro tst; > SQL error: near "fro": syntax error > sqlite> select * from tst; > A|289|A1 > C|29|C1 > A|29|A2 > C|129|C2 > C|19|C3 > A|1129|A3 > B|19|B1 > A|19|A4 > B|9|B2 > B|99|B3 > sqlite> select tst.* from tst cross join > ...> ( select max(score) as maxS, name from tst group by name ) as > subQuery > ...> on tst.name=subQuery.name and tst.score = subQuery.maxS; > C|129|C2 > A|1129|A3 > B|99|B3 > sqlite> > > > Rgds, > Simon > > -- -- Andrea Galligani Macs Tech s.r.l. Via San Paolo 11, 56125 Pisa - Italy Phone...: +39 050 40915 e-mail..: [EMAIL PROTECTED] -- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL question
sqlite> create table t1(n,c);
sqlite> insert into t1 values("a",3);
sqlite> insert into t1 values("a",5);
sqlite> insert into t1 values("b",7);
sqlite> insert into t1 values("b",2);
sqlite> select * from t1;
a|3
a|5
b|7
b|2
sqlite> select n,max(c) from t1 group by n;
a|5
b|7
--
[image: Just A Little Bit Of
Geekness]
Le tre grandi virtù di un programmatore: pigrizia, impazienza e arroganza.
(Larry Wall).
On Fri, Jun 6, 2008 at 11:03 AM, Andrea Galligani <
[EMAIL PROTECTED]> wrote:
> Hi to all,
>
> I'm a novice in SQL and SQLite so I apologize if this question has an
> obvious solution
>
> I have a table formed in this way.
>
> NAME SCORE INFO1 INFO2 etc.
> Andrew 5 aaa bbb ...
> Andrew 8 ddd eee ...
> Paul 4 xxx yyy ...
> Paul 6 aaa fff ...
>
> I need a query to get from any name the row with the max score. So from
> the above sample, I would like to obtain the following rows.
>
> NAME SCORE INFO1 INFO2 etc.
> Andrew 8 ddd eee ...
> Paul 6 aaa fff ...
>
> Can I get this result using a complex query or should I filter the rows
> using any different tool from SQL (C/C++)?
>
> Thanks in advance
> Andrea
>
> --
> --
> Andrea Galligani
>
> Macs Tech s.r.l.
> Via San Paolo 11, 56125
> Pisa - Italy
>
> Phone...: +39 050 40915
> e-mail..: [EMAIL PROTECTED]
> --
>
> ___
> 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] SQL question
Andrea, This appears to do what you want... SQLite version 3.4.2 Enter ".help" for instructions sqlite> sqlite> create table tst( name text, score integer, info text ); sqlite> insert into tst values( 'A', 289, 'A1' ); sqlite> insert into tst values( 'C', 29, 'C1' ); sqlite> insert into tst values( 'A', 29, 'A2' ); sqlite> insert into tst values( 'C', 129, 'C2' ); sqlite> insert into tst values( 'C', 19, 'C3' ); sqlite> insert into tst values( 'A', 1129, 'A3' ); sqlite> insert into tst values( 'B', 19, 'B1' ); sqlite> insert into tst values( 'A', 19, 'A4' ); sqlite> insert into tst values( 'B', 9, 'B2' ); sqlite> insert into tst values( 'B', 99, 'B3' ); sqlite> sqlite> select * fro tst; SQL error: near "fro": syntax error sqlite> select * from tst; A|289|A1 C|29|C1 A|29|A2 C|129|C2 C|19|C3 A|1129|A3 B|19|B1 A|19|A4 B|9|B2 B|99|B3 sqlite> select tst.* from tst cross join ...> ( select max(score) as maxS, name from tst group by name ) as subQuery ...> on tst.name=subQuery.name and tst.score = subQuery.maxS; C|129|C2 A|1129|A3 B|99|B3 sqlite> Rgds, Simon 2008/6/6 Andrea Galligani <[EMAIL PROTECTED]>: > Hi to all, > > I'm a novice in SQL and SQLite so I apologize if this question has an > obvious solution > > I have a table formed in this way. > > NAME SCORE INFO1 INFO2 etc. > Andrew 5 aaa bbb ... > Andrew 8 ddd eee ... > Paul 4 xxx yyy ... > Paul 6 aaa fff ... > > I need a query to get from any name the row with the max score. So from > the above sample, I would like to obtain the following rows. > > NAME SCORE INFO1 INFO2 etc. > Andrew 8 ddd eee ... > Paul 6 aaa fff ... > > Can I get this result using a complex query or should I filter the rows > using any different tool from SQL (C/C++)? > > Thanks in advance > Andrea > > -- > -- > Andrea Galligani > > Macs Tech s.r.l. > Via San Paolo 11, 56125 > Pisa - Italy > > Phone...: +39 050 40915 > e-mail..: [EMAIL PROTECTED] > -- > > ___ > 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
[sqlite] Binding Date value in Prepare/bind
Hello SQLite users,
I have two questions, could anyone please help me out.
1. Can I use sqlite3_prepare_v2 in Version 3.1.3?
2. How do I bind date values using prepare/bind methods?
Eg: INSERT INTO TABLE Info(Name,DOB)
values('XYZ',julianday('1984-03-03'));
For above example I can write a prepared statement as
INSERT INTO TABLE Info(Name,DOB) values(?,julianday(?))
And bind 1 & 2 parameters to text values.
But how do I write prepared statement if I want to insert date('now') value
into the table, like below query
INSERT INTO TABLE Info(Name,DOB) values('XYZ',julianday(date('now'))
Will adding julianday(date(?)) cause any performance impact when I supply
the date itself as a value rather than date('now').
I am asking this because julianday(date('1984-03-03')) =
julianday('1984-03-03'). Right?
I am inserting some thousands of rows.
Thanks in advance for your valuable time,
--
Bharath
---
Robosoft Technologies - Come home to Technology
Disclaimer: This email may contain confidential material. If you were not an
intended recipient, please notify the sender and delete all copies. Emails to
and from our network may be logged and monitored. This email and its
attachments are scanned for virus by our scanners and are believed to be safe.
However, no warranty is given that this email is free of malicious content or
virus.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
