On 9/07/2009 2:21 PM, Rick Ratchford wrote:
>
> Okay, this worked, but I have NO IDEA why.
>
> SQLString = "SELECT min(Year) FROM TmpTable " & _
> "WHERE Month=1 UNION " & _
> "SELECT max(Year) FROM TmpTable " & _
> "WHERE Month = 12 LIMIT 2"
>
>
> While this returned the correct answers:
>
> 1988
> 2008
>
> What I don't understand is why it didn't simply return:
>
> 1988
> 1988
>
> Since there is at least 15 or more days in Month=1 (Jan).
>
> Anyone?
>
> Thanks.
> Rick
>
>
>
> ----Original Message-----
> From: sqlite-users-boun...@sqlite.org
> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Rick Ratchford
> Sent: Wednesday, July 08, 2009 11:17 PM
> To: 'General Discussion of SQLite Database'
> Subject: [sqlite] SQL Syntax
>
> I've been trying all kinds of different ways to accomplish the following,
> and my head is spinning.
>
> Problem: How do you return from the DB just the YEAR of the first and last
> YEAR that had dates from 1st week of January to last week of December?
>
> Clarification: Suppose you had a database that contained stock price data.
> You are only interested in the first and last year that was a complete year.
>
> A "complete year" is a year where you have price data from the very first
> weekday (not weekend or holiday) of the year to the very last weekday of
> that year. Usually, a complete year is from Jan 2, 3 or 4 to Dec 29, 30, 31.
>
>
> Result Desired: To simply return the YEAR of the first complete year, and
> the YEAR of the last complete year of the dataset. This should return only
> two years in a single column. If 1988 is the first complete year and 2008 is
> the last complete year, then it should only return:
>
> 1988
> 2008
>
> Available columns are:
>
> DATE (complete date)
> YEAR
> MONTH
> DAY
> ...
>
>
> I'm trying to do something like this, but it won't work because it says you
> can only have one LIMIT clause.
>
> SQLString = "SELECT min(Year) FROM TmpTable " & _
> "WHERE Month=1 LIMIT 1 UNION " & _
> "SELECT max(Year) FROM TmpTable " & _
> "WHERE Month = 12 LIMIT 1"
>
> Help would be appreciated.
>
Each SELECT will return only 1 result. Split that up into 2 queries
(omit the LIMIT clause; it's redundant) and see for yourself.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
