On 21 Jun 2010, at 3:16am, P Kishor wrote:
> The above brings up a related issue... At least Pg docs suggest that
> LIMIT should always be used with ORDER BY. See
> http://developer.postgresql.org/pgdocs/postgres/sql-select.html
>
> "When using LIMIT, it is a good idea to use an ORDER BY clause
On Sun, Jun 20, 2010 at 9:21 PM, Sam Carleton
wrote:
> SELECT FolderId, ImageId, instertedon FROM V_FAVORITES_SELECTED
> WHERE case when instertedon > julianday(@time)
> then findLargeImage(@path, FolderId, ImageId)
> else 0 end;
I think Igor wants you to add the lIMIT clause at the end of t
On Sun, Jun 20, 2010 at 9:56 PM, Igor Tandetnik wrote:
> Sam Carleton wrote:
>> On Sun, Jun 20, 2010 at 5:23 PM, Igor Tandetnik wrote:
>>
>>> In what way did the statement I gave you, exactly as written, fail to
>>> satisfy your requirements?
>>
>> Igor,
>>
>> When I put in EXACTLY what you gav
On Sun, Jun 20, 2010 at 8:55 PM, Pavel Ivanov wrote:
>> I think that is a common assumption, but a wrong one. Think about it
>> -- the sql engine has to get the entire result set back before it can
>> apply the limit clause.
>
> Puneet, are you 100% sure about that or are you just telling your
> o
Sam Carleton wrote:
> On Sun, Jun 20, 2010 at 5:23 PM, Igor Tandetnik wrote:
>
>> In what way did the statement I gave you, exactly as written, fail to
>> satisfy your requirements?
>
> Igor,
>
> When I put in EXACTLY what you gave me:
>
> SELECT FolderId, ImageId, instertedon FROM V_FAVORIT
> I think that is a common assumption, but a wrong one. Think about it
> -- the sql engine has to get the entire result set back before it can
> apply the limit clause.
Puneet, are you 100% sure about that or are you just telling your
opinion? Just recently there was another thread where "strange"
On Sun, Jun 20, 2010 at 7:41 PM, P Kishor wrote:
> Once again, did you try Igor's suggestion? Exactly as he suggested?
> Don't know if it will work or not, but this I can say -- if there is
> one person in the entire SQL world you should listen to very
> carefully, it is Igor. There is never any
On Sun, Jun 20, 2010 at 7:53 PM, Simon Slavin wrote:
>
> On 21 Jun 2010, at 12:41am, P Kishor wrote:
>
>> iirc, LIMIT 1 is applied *after* the WHERE clause is satisfied. In
>> other words, the entire result set is returned, and then it is
>> LIMITed. So, the behavior is correct.
>
> That does agre
On 21 Jun 2010, at 12:41am, P Kishor wrote:
> iirc, LIMIT 1 is applied *after* the WHERE clause is satisfied. In
> other words, the entire result set is returned, and then it is
> LIMITed. So, the behavior is correct.
That does agree with what Sam is reporting. However, I am surprised at this
On Sun, Jun 20, 2010 at 5:16 PM, Simon Slavin wrote:
>
> On 20 Jun 2010, at 11:08pm, Sam Carleton wrote:
>
>> Simon,
>>
>> this is a direct CUT and PASTE from my code:
>>
>> #define SQL_GET_NEXT_SLIDE_SHOW_IMAGE \
>> "SELECT FolderId, ImageId, instertedon " \
>> "FROM V_FAVORITES_SELECT
On 20 Jun 2010, at 11:08pm, Sam Carleton wrote:
> Simon,
>
> this is a direct CUT and PASTE from my code:
>
> #define SQL_GET_NEXT_SLIDE_SHOW_IMAGE \
> "SELECT FolderId, ImageId, instertedon " \
> "FROM V_FAVORITES_SELECTED " \
> "WHERE instertedon > julianday(@time) AND findLarg
Simon,
this is a direct CUT and PASTE from my code:
#define SQL_GET_NEXT_SLIDE_SHOW_IMAGE \
"SELECT FolderId, ImageId, instertedon " \
"FROM V_FAVORITES_SELECTED " \
"WHERE instertedon > julianday(@time) AND findLargeImage(@path,
FolderId, ImageId) = 1 " \
"LIMIT 1"
When
On 20 Jun 2010, at 10:40pm, Sam Carleton wrote:
> I am getting the EXACT same result, it calls findImage on for EVERY
> row in the result set. The goal is to have the findImage()
> short-circuit the where clause so that once it returns 1 one time, it
> stops. Aka lots of 0's can be returned, bu
On Sun, Jun 20, 2010 at 5:23 PM, Igor Tandetnik wrote:
> In what way did the statement I gave you, exactly as written, fail to satisfy
> your requirements?
Igor,
When I put in EXACTLY what you gave me:
SELECT FolderId, ImageId, instertedon FROM V_FAVORITES_SELECTED
WHERE case when instertedon
Sam Carleton wrote:
> On Sun, Jun 20, 2010 at 4:52 PM, P Kishor wrote:
>>
>> No. WHERE clause is a completely different part of the statement,
>> different from WHEN which is a part of the CASE construct. "CASE..
>> WHEN .. THEN .. ELSE .. END" is one construct, an expression, and
>> applies to
Lord, my response was so scrambled... my apologies. Let me try again...
CASE.. WHEN.. THEN.. ELSE.. END is an expression. You can use it in
lieu of simple columns, and you can use it wherever columns can be
used. So, you can use it where you have <> below
SELECT <>
FROM table(s)
WHERE <>
Go back
On Sun, Jun 20, 2010 at 4:52 PM, P Kishor wrote:
>
> No. WHERE clause is a completely different part of the statement,
> different from WHEN which is a part of the CASE construct. "CASE..
> WHEN .. THEN .. ELSE .. END" is one construct, an expression, and
> applies to the columns, while WHEN is a
On Sun, Jun 20, 2010 at 3:47 PM, Sam Carleton
wrote:
> Erin and Igor,
>
> I simply cannot wrap my head around the correct syntax. BAsed on the
> documentation, I believe I should be looking to make it fit this
> pattern:
>
> CASE WHEN x=w1 THEN r1 WHEN x=w2 THEN r2 ELSE r3 END
>
> Does the WHERE
Erin and Igor,
I simply cannot wrap my head around the correct syntax. BAsed on the
documentation, I believe I should be looking to make it fit this
pattern:
CASE WHEN x=w1 THEN r1 WHEN x=w2 THEN r2 ELSE r3 END
Does the WHERE clause get replaced with the WHEN clause like this:
CASE SELECT Fold
Not literally enough, in fact, as Erin ably explained.
Igor Tandetnik
Sam Carleton wrote:
> You lost me;) I think I might be taking you too literally, here is
> what I tried:
>
> SELECT FolderId, ImageId, instertedon
> FROM V_FAVORITES_SELECTED
> WHERE instertedon > julianday(@time)
> THEN f
You missed the "case when" part.
See "The CASE Expression" : http://www.sqlite.org/lang_expr.html
On Mon, Jun 21, 2010 at 2:28 AM, Sam Carleton
wrote:
> Igor,
>
> You lost me;) I think I might be taking you too literally, here is
> what I tried:
>
> SELECT FolderId, ImageId, instertedon
> FROM
Igor,
You lost me;) I think I might be taking you too literally, here is
what I tried:
SELECT FolderId, ImageId, instertedon
FROM V_FAVORITES_SELECTED
WHERE instertedon > julianday(@time)
THEN findImage(@rootPath, FolderId, ImageId) ELSE 0 END;
The prepare statement didn't much like somethi
Sam Carleton wrote:
> This works great. The issue is that the image returned might NOT
> exist anymore, so I created an extension function to return 1 if it
> exists, 0 if it doesn't, findImage( RootPath, FolderId, ImageId).
> Then I added to the select statement like this:
>
> SELECT FolderId,
I am working on a slide show feature. The logic is based on time,
there are a set of image metadata (folderId and filename) add to the
DB, the insertion time is also saved. The client calls the
getNextSlideShow() method to get the next image. The current select
statement is:
SELECT FolderId, Im
24 matches
Mail list logo
