>I hope some of the SQL gurus here can get me on the right track. How do I write a view that does this:
>
>If I had a CITIES table, with a 1-to-many relationship with TEMPERATURE table. TEMPERATURE simply contains a Temperature, a Date, and of course the CITY's ID. For fun, let's say I've entered in one record for each city, each day, for the last 2 years. (So after 2 years, each city would have 730 TEMPERATURE records)
>
>How would I write a view that would return the all-time high temperature for each city? (If I had 250 cities, I'd want 250 records sent back,.... one for each city)
>Simplest format: CITY NAME, TEMP, DATE.
>
>
Well, I couldn't get it into a single SELECT statement for a view, but I got it into 2:

select cities.iid, cities.cname, max(temps.ntemp) as nhigh_temp ;
  from cities ;
  join temps ;
  on cities.iid = temps.iid_cities ;
  group by 1 ;
  into cursor cutemps

select cutemps.*, temps.ddate ;
  from cutemps ;
  join temps ;
  on cutemps.iid = temps.iid_cities and cutemps.nhigh_temp = temps.ntemp ;
  order by cutemps.cname ;
  into cursor cu_tempanddate

This won't necessarily return exactly 250 records, since the possibility exists that the same highest temp was reached on more than one day in a particular city.


>2nd question: what resources could you refer me to that would help me out with SQL statements like the one I wrote above? I'm not really interested in a SQL Bible, but more like what an Algorithm Book would do for a coder.... examples of how to do this weird thing, examples of how to do that,etc. The Vfp Help/MSDN seems to be really lacking. They had a good start, and attempted to show examples of each command, but not how to mix it up, use subqueries, etc.
>

I don't know that there is one for VFP SQL. SQL Server and Oracle have pretty good book, but the SQL sytax in each of those can be vastly different than VFP.


>Thanks,
>Dustin