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"
Read about aggregate functions (min() is one of those) in your favorite SQL
textbook, then drop the LIMIT clause. You may also want to use UNION ALL
instead of UNION: otherwise, if you just have one full year's worth of data,
you'll get a single row as UNION removes duplicates.
In fact, it may be more convenient (definitely easier to reuse as a fragment
in more complicated queries) to return this result as one row with two
columns:
select
(select min(Year) ...) as firstFullYear,
(select max(Year) ...) as lastFullYear;
>Igor Tandetnik
-------------------------------------------------------------
SQLString = "SELECT min(Year) FROM TmpTable " & _
"WHERE Month=1 And Day<8 " & _
"UNION ALL " & _
"SELECT max(Year) FROM TmpTable " & _
"WHERE Month = 12 And Day>24 "
This should limit it to looking for something in the first week and last
week.
David
-------------------------------------------------------------
<Warning> Noobie here </Warning>
I think the min and max functions by design return only one value.
However, I'm afraid this query might not work in the general case.
For example, if your data starts on january 20, 1988, then this query will
think 1988 is a full year. Ditto if the data ends early in december.
So it seems to me that it works for you by luck, because your data set
doesn't start in january, nor ends in december.
Unless of course if I am totally wrong, which happens often enough.
Jean-Denis Muys
-------------------------------------------------------------
In one message, I'd like to thank you all for your help (Igor, David,
Jean-Denis, ...).
Igor, that's a good precaution to use UNION ALL, although in case of my code
this would never happen since it has a minimum 5 years of data requirement
even to run the procedure. Thanks for that pointer as well as the idea to
condense by reply by having both years appear in one row. I've not reached
the point in my 'book' where you group 'select' inside 'select' using
parenthesis. Yours was actually the first time I've seen it. Thanks. :)
David, thanks for your input on the addition of Day <>. :)
Jean-Denis, newbie or not, thanks for your input. I did note this and had
originally placed a Day < 5 for the beginning year and Day > 28 for the last
year. But with all the tweeking I was doing to this, some things dropped,
some added... I'll use David's 'first week, last week' numbers for the
final.
Thanks guys.
Rick
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
