Re: [sqlite] Little SQL help please..

[johnny depp (really!)]

Ok, thats good to know!

thx Igor!





Dan Kennedy-4 wrote:
> 
> 
> On Feb 18, 2009, at 1:27 AM, johnny depp (really!) wrote:
> 
>>
>> I didn't run it yet, the idea of using min(col1) = max(col1) was all I
>> needed.
>> I assumed it was incorrect because I thought
>> referring to an ungrouped column in a group by was incorrect,  
>> because the
>> ungrouped col would represent a set, and not a value...
>>
>> I just ran this:
>>
>> select c2 from
>> (select 1 as c1, 1 as c2
>> union
>> select 1 as c1, 2 as c2
>> union
>> select 1 as c1, 3 as c2)
>> group by c1
>>
>> and it returns 3.
>>
>> I think this is incorrect behaviour and should not compile because
>> the result of c2 is clearly {1, 2, 3}...or am I wrong about this?
>> Is this standard SQL languages behaviour?
> 
> You are correct, according to SQL this is invalid. But SQLite and other
> database engines allow it because it is convenient. In such a situation
> SQLite selects a value arbitrarily from the set to return.
> 
> 
> 
> 
> 
>> Igor Tandetnik wrote:
>>>
>>> "johnny depp (really!)"
>>>  wrote in
>>> message news:22057169.p...@talk.nabble.com
 You probably meant:

 select col1, case when min(col2) = max(col2) then min(col2) else  
 'not
 the same' end
 from mytable group by col1;
>>>
>>> It works for me as originally written. Do you get any errors?
>>>
>>> Igor Tandetnik
>>>
>>>
>>>
>>> ___
>>> sqlite-users mailing list
>>> sqlite-users@sqlite.org
>>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>>
>>>
>>
>> -- 
>> View this message in context:
>> http://www.nabble.com/Little-SQL-help-please..-tp22052925p22063324.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
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 

-- 
View this message in context: 
http://www.nabble.com/Little-SQL-help-please..-tp22052925p22075596.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] Little SQL help please..

[Dan]

On Feb 18, 2009, at 1:27 AM, johnny depp (really!) wrote:

>
> I didn't run it yet, the idea of using min(col1) = max(col1) was all I
> needed.
> I assumed it was incorrect because I thought
> referring to an ungrouped column in a group by was incorrect,  
> because the
> ungrouped col would represent a set, and not a value...
>
> I just ran this:
>
> select c2 from
> (select 1 as c1, 1 as c2
> union
> select 1 as c1, 2 as c2
> union
> select 1 as c1, 3 as c2)
> group by c1
>
> and it returns 3.
>
> I think this is incorrect behaviour and should not compile because
> the result of c2 is clearly {1, 2, 3}...or am I wrong about this?
> Is this standard SQL languages behaviour?

You are correct, according to SQL this is invalid. But SQLite and other
database engines allow it because it is convenient. In such a situation
SQLite selects a value arbitrarily from the set to return.





> Igor Tandetnik wrote:
>>
>> "johnny depp (really!)"
>>  wrote in
>> message news:22057169.p...@talk.nabble.com
>>> You probably meant:
>>>
>>> select col1, case when min(col2) = max(col2) then min(col2) else  
>>> 'not
>>> the same' end
>>> from mytable group by col1;
>>
>> It works for me as originally written. Do you get any errors?
>>
>> Igor Tandetnik
>>
>>
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>>
>
> -- 
> View this message in context: 
> http://www.nabble.com/Little-SQL-help-please..-tp22052925p22063324.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

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

Re: [sqlite] Little SQL help please..

[johnny depp (really!)]

I didn't run it yet, the idea of using min(col1) = max(col1) was all I
needed.
I assumed it was incorrect because I thought
referring to an ungrouped column in a group by was incorrect, because the
ungrouped col would represent a set, and not a value...

I just ran this:

select c2 from
(select 1 as c1, 1 as c2
 union
 select 1 as c1, 2 as c2
 union
 select 1 as c1, 3 as c2)
 group by c1

and it returns 3.

I think this is incorrect behaviour and should not compile because
the result of c2 is clearly {1, 2, 3}...or am I wrong about this?
Is this standard SQL languages behaviour?






Igor Tandetnik wrote:
> 
> "johnny depp (really!)"
>  wrote in
> message news:22057169.p...@talk.nabble.com
>> You probably meant:
>>
>> select col1, case when min(col2) = max(col2) then min(col2) else 'not
>> the same' end
>> from mytable group by col1;
> 
> It works for me as originally written. Do you get any errors?
> 
> Igor Tandetnik 
> 
> 
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 

-- 
View this message in context: 
http://www.nabble.com/Little-SQL-help-please..-tp22052925p22063324.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] Little SQL help please..

[Hoover, Jeffrey]

Use the min function (since they are all have the same value)

-Jeff

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of johnny depp
(really!)
Sent: Tuesday, February 17, 2009 3:44 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] Little SQL help please..


Hello,

I'm investigating if i should uses sqlite from within java?
I need an embedded database, no fancy user management or anything... I'm
only interested in speed.
Sqlite looks good for the job..So I went out and found the someone has
written a java sqlite wrapper.
Great! 
But I ran into a problem:

I need to group some rows, and if the values for a column are all the
same I
want to return that value,
else I want to return "not the same".. like so:

For this table:

col1, col2
A  "1"
A  "2"
B  "1"
B  "1"

And the query: select . group by col1; // can't write the query
because
I don't know it..

I would like the result to be:
col1 col2
A "not the same"
B "1"

I think the query should be this:

SELECT col1, 
   CASE count(distinct col2) > 1 THEN "not the same"
ELSE  first(col2)
END 
   AS col2
FROM table GROUP BY col1

The problem is the aggregation function first does not exist,
this would not be a problem in c or c++ because I would plug it in the
system (I read),
the problem is I'm calling sqlite from java and so I can't plug in my
own
functions..
Or I will have to look into jni and i don't want to do that..seem scary
stuff..

So my questions is, is there another way to do it without using the
(none
existing) function first?


Kind Regards,
Nick.






























-- 
View this message in context:
http://www.nabble.com/Little-SQL-help-please..-tp22052925p22052925.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
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Little SQL help please..

[Igor Tandetnik]

"johnny depp (really!)"
 wrote in
message news:22057169.p...@talk.nabble.com
> You probably meant:
>
> select col1, case when min(col2) = max(col2) then min(col2) else 'not
> the same' end
> from mytable group by col1;

It works for me as originally written. Do you get any errors?

Igor Tandetnik 



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

Re: [sqlite] Little SQL help please..

[johnny depp (really!)]

You probably meant:

select col1, case when min(col2) = max(col2) then min(col2) else 'not the
same' end
from mytable group by col1; 

But thx a lot Igor!



Igor Tandetnik wrote:
> 
> "johnny depp (really!)"
>  wrote in
> message news:22052925.p...@talk.nabble.com
>> I need to group some rows, and if the values for a column are all the
>> same I want to return that value,
>> else I want to return "not the same".. like so:
>>
>> For this table:
>>
>> col1, col2
>> A  "1"
>> A  "2"
>> B  "1"
>> B  "1"
>>
>> And the query: select . group by col1; // can't write the query
>> because I don't know it..
>>
>> I would like the result to be:
>> col1 col2
>> A "not the same"
>> B "1"
> 
> select col1, case when min(col2) = max(col2) then col2 else 'not the 
> same' end
> from mytable group by col1;
> 
> Igor Tandetnik 
> 
> 
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 

-- 
View this message in context: 
http://www.nabble.com/Little-SQL-help-please..-tp22052925p22057169.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] Little SQL help please..

[Igor Tandetnik]

"johnny depp (really!)"
 wrote in
message news:22052925.p...@talk.nabble.com
> I need to group some rows, and if the values for a column are all the
> same I want to return that value,
> else I want to return "not the same".. like so:
>
> For this table:
>
> col1, col2
> A  "1"
> A  "2"
> B  "1"
> B  "1"
>
> And the query: select . group by col1; // can't write the query
> because I don't know it..
>
> I would like the result to be:
> col1 col2
> A "not the same"
> B "1"

select col1, case when min(col2) = max(col2) then col2 else 'not the 
same' end
from mytable group by col1;

Igor Tandetnik 



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

Re: [sqlite] Little SQL help please..

[Daniel Lidström]

From: johnny depp (really!) [mailto:nick_reyntj...@hotmail.com] 
> 
> Hello,
> 
> I'm investigating if i should uses sqlite from within java?
> I need an embedded database, no fancy user management or 
> anything... I'm
> only interested in speed.
> Sqlite looks good for the job..So I went out and found the someone has
> written a java sqlite wrapper.
> Great! 
> But I ran into a problem:
> 
> I need to group some rows, and if the values for a column are 
> all the same I
> want to return that value,
> else I want to return "not the same".. like so:
> 
> For this table:
> 
> col1, col2
> A  "1"
> A  "2"
> B  "1"
> B  "1"
> 
> And the query: select . group by col1; // can't write the 
> query because
> I don't know it..
> 
> I would like the result to be:
> col1 col2
> A "not the same"
> B "1"

Can't you do some post-processing from within Java? Seems like an easy thing
to do with some code.

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