Query - Samples

Owned by Alain Savoie
Last updated: Jun 14, 2024
7 min read

Since the tags aren't as obvious using query, you can always assign the AS statement    ie: storyText as story.content

Stories Samples




Brexit StoriesAll Stories that include mention of BrexitSELECT storyText, header FROM story WHERE storyText like '%brexit%'
Hilary Clinton StoriesAll Stories that include mention of ClintonSELECT storyText, header FROM story WHERE storyText like '%clinton%'
AP StoriesAll AP TopicsSELECT S.storyText AS story, S.header AS headline, T.name as topic
FROM story S
JOIN story_topic T ON S.topicID=T.ID
WHERE T.name IN ('AP Business', 'AP Entertainment', 'AP Politics', 'AP Top Headlines')
AND S.output=1
ORDER BY topic
Stories - First Sentence OnlyStories and include only the first sentence. SELECT
IF(LENGTH(SUBSTR(S.storyText, 1, POSITION('.' IN S.storyText))) > 0,
SUBSTR(S.storyText, 1, POSITION('.' IN S.storyText)),
S.storyText) AS header, T.broadcastname
FROM story S
JOIN story_topic T ON S.topicID=T.ID
WHERE T.name=@Topic
Stories - Single Paragraph OnlyFirst paragraph only of each storySELECT
IF(LENGTH(SUBSTR(S.storyText, 1, POSITION('\n' IN S.storyText))) > 0,
SUBSTR(S.storyText, 1, POSITION('\n' IN S.storyText)),
S.storyText) AS header, T.broadcastName
FROM story S
JOIN story_topic T ON S.topicID=T.ID
WHERE T.name=@Topic
Stories from a specific topicStories from a specific topic using parameters

SELECT S.storyText, S.header, T.broadcastName
FROM story S, story_topic T
WHERE S.topicid=T.ID AND T.name LIKE @Topic AND S.output=1
/*Example BBC% */

ORDER BY T.broadcastName, S.priority DESC

Sports Samples




Final MLB ScoresAll MLB Scores that are finalSELECT L.name as league, V.location as vlocation, V.code as vcode, V.name as vname, S.vscore as vscore, H.location as hlocation, H.code as hcode, H.name as hname, S.hscore as hscore, S.status FROM score S, team V, team H, league L WHERE S.leagueID = L.id AND S.vteamid = V.id AND S.hteamid = H.id AND S.gamestate = 'Final' AND L.name = 'MLB'
Final ScoresAll scores that have gone finalSELECT L.name, V.location as vlocation, V.code as vcode, V.name as vname, S.vscore, H.location as hlocation, H.code as hcode, H.name as hname, S.hscore, S.status FROM score S, team V, team H, league L WHERE S.leagueID = L.id AND S.vteamid = V.id AND S.hteamid = H.id AND S.gamestate = 'Final'
New York GamesGames that include NYC teamsSELECT L.name, V.location as vlocation, V.code as vcode, V.name as vname, S.vscore, H.location as hlocation, H.code as hcode, H.name as hname, S.hscore, S.status FROM score S, team V, team H, league L WHERE S.leagueID = L.id AND S.vteamid = V.id AND S.hteamid = H.id AND (V.location = 'New York' OR H.location = 'New York')
Scores - NHL Top 10 PlayersScores - NHL Top 10 Players w params

SELECT P.firstname, P.lastname, T.code, T.location, T.name,
'Top 10 Assists' as topic,

MAX(case when TAG.key = @stat then CONVERT(TAG.`value`, UNSIGNED INTEGER) end) as stat,
/*A example for stat*/

MAX(case when TAG.key = 'headshot' then TAG.`value` end) as headshot
FROM
teamplayer_tag TAG
JOIN team_player P on TAG.teamplayerID=P.ID
JOIN team T on P.teamID=T.ID
JOIN league L on L.ID=T.leagueID
WHERE L.code='NHL'
GROUP BY TAG.teamplayerID
ORDER BY stat DESC, P.lastname, P.firstname
LIMIT 10

Scores - NBA Standings DivisionsScores - NBA Standings w ParamsSELECT
MAX(case when TAG.key = 'divisionRank' then CONVERT(TAG.`value`, UNSIGNED INTEGER) end) as ranks,
T.code, T.location, T.name,
MAX(case when TAG.key = 'division' then TAG.`value` end) as division,
MAX(case when TAG.key = 'GP' then TAG.`value` end) as GP,
MAX(case when TAG.key = 'W' then TAG.`value` end) as W,
MAX(case when TAG.key = 'L' then TAG.`value` end) as L,
MAX(case when TAG.key = 'PCT' then TAG.`value` end) as PCT,
MAX(case when TAG.key = 'divisionGB' then TAG.`value` end) as GB,
MAX(case when TAG.key = 'home' then TAG.`value` end) as home,
MAX(case when TAG.key = 'road' then TAG.`value` end) as away,
MAX(case when TAG.key = 'last10' then TAG.`value` end) as L10,
MAX(case when TAG.key = 'streak' then TAG.`value` end) as STRK
FROM team_tag TAG
JOIN team T ON TAG.teamID=T.ID
JOIN league L ON T.leagueID=L.ID
WHERE L.code='NBA'
GROUP BY TAG.teamID
HAVING division=@division
ORDER BY ranks



Custom Samples




Golf QueryGolf Query using parameters

SET NAMES 'utf8mb4' COLLATE 'utf8mb4_unicode_ci';
SELECT P.* FROM
(SELECT `value` AS Y FROM custom_tag WHERE `key`=@prop AND customID=
(SELECT C.ID FROM custom_tag TAG JOIN custom C ON TAG.customID=C.ID JOIN custom_topic T ON C.topicID=T.ID WHERE `key`= 'name' AND `value`=@event AND T.name='Golf Sportsbook')) AS PROP,
JSON_TABLE(
CAST(PROP.Y AS JSON),
'$[*]' COLUMNS(
name VARCHAR(100) PATH '$.name',
odds VARCHAR(100) PATH '$.odds'
)
) AS P;

Go Transit BusesGo Transit BusesSELECT 'Go Transit Buses' as service,
MAX(case when TAG.key = 'zone' then TAG.`value` end) as zone,
MAX(case when TAG.key = 'stop' then TAG.`value` end) as stop,
MAX(case when TAG.key = 'routeID' then TAG.`value` end) as routeID,
MAX(case when TAG.key = 'route' then TAG.`value` end) as route,
MAX(case when TAG.key = 'routeColour' then TAG.`value` end) as routeColour,
MAX(case when TAG.key = 'routeTextColour' then TAG.`value` end) as routeTextColour,
MAX(case when TAG.key = 'routeType' then TAG.`value` end) as routeType,
MAX(case when TAG.key = 'direction1' then TAG.`value` end) as destination1,
MAX(case when TAG.key = 'departureTime1' then TAG.`value` end) as departureTime1,
MAX(case when TAG.key = 'direction2' then TAG.`value` end) as destination2,
MAX(case when TAG.key = 'departureTime2' then TAG.`value` end) as departureTime2,
MAX(case when TAG.key = 'direction3' then TAG.`value` end) as destination3,
MAX(case when TAG.key = 'departureTime3' then TAG.`value` end) as departureTime3
FROM
custom_tag TAG
JOIN custom C on TAG.customID=C.ID
JOIN custom_topic T on C.topicID=T.ID
WHERE T.name='Scotia Bank Arena Public Transit'
GROUP BY TAG.customID
HAVING routeType='bus' AND zone='Toronto_GO' AND destination1 NOT LIKE '%union station%'
ORDER BY route
US Elections Trend ReportsCustom - US Elections Trend Reports w params

SELECT 218 AS majority, partyChip, total,
CASE
WHEN X.p='DEM' THEN 'Democratic'
WHEN X.p<>'DEM' THEN 'Republican'
END AS party
FROM
(SELECT
MAX(case when TAG.key = 'party' then TAG.`value` end) as p,
MAX(case when TAG.key = 'party' then CONCAT(TAG.`value`, '.png') end) as partyChip,
MAX(case when TAG.key = 'Winning Trend' then CONVERT(TAG.`value`, UNSIGNED INTEGER) end) as total
FROM custom_tag TAG
JOIN custom C on TAG.customID=C.ID
JOIN custom_topic T on C.topicID=T.ID
WHERE T.name=@race

/*House Trend Report Example*/

GROUP BY TAG.customID
LIMIT 2) X
ORDER BY total DESC




Elections Samples




Elections - California Proposition 68Elections - California Proposition 68SELECT CANDIDATE.lastname AS choice, CHOICE.votes
FROM elc_contest CONTEST
JOIN elc_event EVENT ON EVENT.ID=CONTEST.eventID
JOIN elc_office OFFICE ON CONTEST.officeID=OFFICE.ID
JOIN elc_choice CHOICE ON CHOICE.contestID=CONTEST.ID
JOIN elc_candidate CANDIDATE ON choice.candidateID=CANDIDATE.ID
WHERE OFFICE.name = 'Proposition 68-Fund Outdoor Access' AND EVENT.name='California State Primary 2018'
ORDER BY CHOICE.votes DESC
Elections - Party Totals Elections - Party Totals params

SELECT * FROM
((SELECT nameShort AS partyCode, name AS partyName, chicklet, COUNT(*) as total
FROM (
SELECT * FROM
(SELECT PARTY.name, PARTY.nameShort, CHOICE.votes, CHOICE.contestID, PARTY.color AS chicklet
FROM elc_candidate CANDIDATE
JOIN elc_party PARTY ON CANDIDATE.partyID=PARTY.ID
JOIN elc_choice CHOICE ON CANDIDATE.ID=CHOICE.candidateID
WHERE CHOICE.contestID IN
(SELECT CONTEST.ID FROM elc_contest CONTEST
JOIN elc_event EVENT ON EVENT.ID=CONTEST.eventID
WHERE EVENT.name=@Event)
ORDER BY contestID, votes DESC) x
GROUP BY contestID) y
GROUP BY nameShort)
UNION
(SELECT distinct P.nameShort AS partyCode, P.name AS partyName, P.color AS chicklet, 0 AS total
FROM elc_party P
JOIN elc_candidate CANDIDATE ON P.ID=CANDIDATE.partyID
JOIN elc_choice CHOICE ON CANDIDATE.ID=CHOICE.candidateID
JOIN elc_contest CONTEST ON CHOICE.contestID=CONTEST.ID
JOIN elc_event EVENT ON EVENT.ID=CONTEST.eventID
WHERE EVENT.name=@Event

/*Alberta 2019*/

AND P.nameShort IN ('UCP', 'NDP', 'AP', 'LIB', 'GRN', 'WRP'))) z
GROUP BY partyCode
ORDER BY total DESC, partyCode

Elections - Electoral College TotalElections - Electoral College TotalSELECT CHOICE.votes, p.name
FROM elc_contest CONTEST
JOIN elc_choice CHOICE ON CHOICE.contestID=CONTEST.ID
JOIN elc_event E ON E.ID=CONTEST.eventID
JOIN elc_office O ON CONTEST.officeID=O.ID
JOIN elc_candidate CANDIDATE ON CHOICE.candidateID=CANDIDATE.ID
JOIN elc_party P ON CANDIDATE.partyID=P.ID
WHERE E.name='US and Statewide Popular and Electoral Votes 2016'
AND O.name='Electoral College'
AND P.nameShort IN ('REP', 'DEM')

Finance Samples




Highest Volume StocksThe 5 highest volume stocksSELECT name, symbol, volume FROM stock ORDER BY volume DESC limit 5
Top Gaining StocksThe 5 top gaining stocks by percentageSELECT name, symbol, volume, priceChange/price as percentChange, price, priceChange FROM stock ORDER BY priceChange/price DESC limit 5
Top Losing StocksThe 5 top losing stocks by percentageSELECT name, symbol, volume, priceChange/price as percentChange, price, priceChange FROM stock ORDER BY priceChange/price ASC limit 5
Market Volume LeadersMarket Volume Leaders using parameters

SELECT symbol, name, volume
FROM stock S
JOIN stock_topic T ON S.topicID=T.ID
WHERE T.market=@market

/*Dow Jones 30 Example*/

ORDER by S.volume DESC
LIMIT 10

JSON Samples

Supported in mySQL version 8.x




JSON extract from a JSON dynamic tag

pull a json feed: @prop


SET NAMES 'utf8mb4' COLLATE 'utf8mb4_unicode_ci';
SET @PROP := 'o_Grid_Position_of_Winner';
SET @LEAGUE := 'F1';
SELECT P.* FROM
(SELECT Y FROM
(SELECT JSON_EXTRACT(X.json, '$.runners') AS Y FROM
(SELECT TAG.`value` AS json FROM league_tag TAG JOIN league L ON TAG.leagueID=L.ID WHERE `key`= @PROP AND L.code=@LEAGUE) AS X) AS Z) AS PROP,
JSON_TABLE(
CAST(PROP.Y AS JSON),
'$[*]' COLUMNS(
name VARCHAR(100) PATH '$.name',
odds VARCHAR(100) PATH '$.odds',
active BOOL PATH '$.active'
)
) AS P;



Tweet Samples




Tweets with Trump
SELECT `tweet`, `fromAccount`, `fromName`, `avatarUrl` FROM tweet WHERE `tweet` like '%trump%'






Weather Samples




Hottest CitiesThe 5 hottest citiesSELECT C.cityName as name, D.temp as temp, CONCAT('file:///D:/',W.`filename`) as media FROM weather W, weather_city C, weather_city_details D WHERE D.detailType = 'current' AND D.cityID = C.id AND w.`ID` = D.`weatherId` ORDER BY D.temp DESC LIMIT 5
The Hottest CityThe hottest citySELECT C.cityName, D.temp, D.shortText FROM weather_city C, weather_city_details D WHERE D.detailType = 'current' AND D.cityID = C.id ORDER BY D.temp DESC LIMIT 1
The Coldest CityThe coldest citySELECT C.cityName, D.temp, D.shortText FROM weather_city C, weather_city_details D WHERE D.detailType = 'current' AND D.cityID = C.id ORDER BY D.temp ASC LIMIT 1
7 Day Forecast7 Day Forecast using parametersSELECT C.cityName,
MAX(CASE WHEN W.detailType = 'current' THEN 'Current' END) AS day0,
MAX(CASE WHEN W.detailType = 'current' THEN W.temp END) AS temp0,
MAX(CASE WHEN W.detailType = 'current' THEN X.type END) AS condition0,
CONCAT('d:/chameleon/', MAX(CASE WHEN W.detailType = 'current' THEN X.filename END)) AS icon0,
MAX(CASE WHEN W.detailType = 'day1' THEN W.dayOfWeek END) AS day1,
MAX(CASE WHEN W.detailType = 'day1' THEN W.temp END) AS temp1,
MAX(CASE WHEN W.detailType = 'day1' THEN W.temp2 END) AS low1,
MAX(CASE WHEN W.detailType = 'day1' THEN W.pop END) AS pop1,
MAX(CASE WHEN W.detailType = 'day1' THEN X.type END) AS condition1,
CONCAT('d:/chameleon/', MAX(CASE WHEN W.detailType = 'day1' THEN X.filename END)) AS icon1,
MAX(CASE WHEN W.detailType = 'day2' THEN W.dayOfWeek END) AS day2,
MAX(CASE WHEN W.detailType = 'day2' THEN W.temp END) AS temp2,
MAX(CASE WHEN W.detailType = 'day2' THEN W.temp2 END) AS low2,
MAX(CASE WHEN W.detailType = 'day2' THEN W.pop END) AS pop2,
MAX(CASE WHEN W.detailType = 'day2' THEN X.type END) AS condition2,
CONCAT('d:/chameleon/', MAX(CASE WHEN W.detailType = 'day2' THEN X.filename END)) AS icon2,
MAX(CASE WHEN W.detailType = 'day3' THEN W.dayOfWeek END) AS day3,
MAX(CASE WHEN W.detailType = 'day3' THEN W.temp END) AS temp3,
MAX(CASE WHEN W.detailType = 'day3' THEN W.temp2 END) AS low3,
MAX(CASE WHEN W.detailType = 'day3' THEN W.pop END) AS pop3,
MAX(CASE WHEN W.detailType = 'day3' THEN X.type END) AS condition3,
CONCAT('d:/chameleon/', MAX(CASE WHEN W.detailType = 'day3' THEN X.filename END)) AS icon3,
MAX(CASE WHEN W.detailType = 'day4' THEN W.dayOfWeek END) AS day4,
MAX(CASE WHEN W.detailType = 'day4' THEN W.temp END) AS temp4,
MAX(CASE WHEN W.detailType = 'day4' THEN W.temp2 END) AS low4,
MAX(CASE WHEN W.detailType = 'day4' THEN W.pop END) AS pop4,
MAX(CASE WHEN W.detailType = 'day4' THEN X.type END) AS condition4,
CONCAT('d:/chameleon/', MAX(CASE WHEN W.detailType = 'day4' THEN X.filename END)) AS icon4,
MAX(CASE WHEN W.detailType = 'day5' THEN W.dayOfWeek END) AS day5,
MAX(CASE WHEN W.detailType = 'day5' THEN W.temp END) AS temp5,
MAX(CASE WHEN W.detailType = 'day5' THEN W.temp2 END) AS low5,
MAX(CASE WHEN W.detailType = 'day5' THEN W.pop END) AS pop5,
MAX(CASE WHEN W.detailType = 'day5' THEN X.type END) AS condition5,
CONCAT('d:/chameleon/', MAX(CASE WHEN W.detailType = 'day5' THEN X.filename END)) AS icon5,
MAX(CASE WHEN W.detailType = 'day6' THEN W.dayOfWeek END) AS day6,
MAX(CASE WHEN W.detailType = 'day6' THEN W.temp END) AS temp6,
MAX(CASE WHEN W.detailType = 'day6' THEN W.temp2 END) AS low6,
MAX(CASE WHEN W.detailType = 'day6' THEN W.pop END) AS pop6,
MAX(CASE WHEN W.detailType = 'day6' THEN X.type END) AS condition6,
CONCAT('d:/chameleon/', MAX(CASE WHEN W.detailType = 'day6' THEN X.filename END)) AS icon6
FROM weather_city_details W
JOIN weather_city C ON C.id=W.cityID
JOIN weather X ON X.ID=W.weatherID
WHERE W.detailType IN ('current', 'day1', 'day2', 'day3', 'day4', 'day5', 'day6') AND
C.cityName=@City
GROUP BY W.cityID

Branding Schedule Samples




Prime Time Schedule LineupPrime Time ScheduleSELECT P.name, P.hashtag, S.start FROM channel C, program P, program_schedule S
WHERE S.programID=P.ID AND S.channelID=C.ID AND C.name='BL Demo Channel'
AND Date(S.start)=Date(now()) AND Time(S.start)>Time('19:00:00') AND Time(S.start)<Time('23:00:00')


SELECT P.name, P.hashtag, S.start FROM channel C, program P, program_schedule S


WHERE S.programID=P.ID AND S.channelID=C.ID AND C.name='BL Demo Channel'


AND Date(S.start)=Date(now()) AND Time(S.start)>Time('19:00:00')

Branding - Next on Channel

Next on Channel using parameters

SELECT P.name, S.start FROM channel C, program P, program_schedule S
WHERE S.programID=P.ID AND S.channelID=C.ID AND C.name=@Channel

/*Demo Central XPR*/

AND S.start > now()
ORDER BY S.start LIMIT 1

In this section:


BannisterLake.com