Limit query result to the first of each group

Question

I'm using YQL to get the today's high and low temperatures in multiple cities. The following query will unnecessarily return a 10-day forecast for each city.

select item.forecast.high, item.forecast.low from weather.forecast 
where woeid in (select woeid from geo.places(1) where text in ("ushuaia, ag", "dallas, tx"))

What I need is found under Channels 0 and 10 of the results (i.e. the first of each city). How can I limit the results to those two?

score 1 · Answer 1 · answered Aug 14 '16 at 09:51

You could join a subquery which return the last two dates in descending order for the matching city code. You need to join on the city code and on the forecast id.

The below sql is T-SQL but I imagine there will be something similar in YQL.

select item.forecast.high, 
   item.forecast.low 
from weather.forecast forecast
JOIN (SELECT TOP 2 id, woeid, date
  FROM weather.forecast
  ORDER BY date DESC) dates ON dates.woeid = forecast.woeid
                            AND dates.id = forecast.id
where woeid in (select woeid 
                from geo.places(1) 
                where text in ("ushuaia, ag", "dallas, tx"))

Thank you for trying to help. Being brand new to database, I didn't quite understand your answer. I'll come back to it one day, but luckily for now, I've found a simpler method. — bongbang, Aug 14 '16 at 20:21

score 0 · Accepted Answer · answered Aug 14 '16 at 20:14

The following query gives me what I want.

select item.forecast.high, item.forecast.low from weather.forecast 
where woeid in (select woeid from geo.places(1) where text in ("ushuaia, ag", "dallas, tx")) 
and item.forecast.date="14 Aug 2016"

I don't know why Yahoo's main YQL console (linked in the question) doesn't work with weather queries as of this moment, but you can still try the query on its weather API page.

Result:

{
 "query": {
  "count": 2,
  "created": "2016-08-14T20:13:35Z",
  "lang": "en-US",
  "results": {
   "channel": [
    {
     "item": {
      "forecast": {
       "high": "25",
       "low": "15"
      }
     }
    },
    {
     "item": {
      "forecast": {
       "high": "88",
       "low": "75"
      }
     }
    }
   ]
  }
 }
}

Limit query result to the first of each group

2 Answers2