SportsDB Home
Documentation
Specification
Samples
Query Library
SportsCaster Web Service
Hosted on SourceForge
and licensed under
Creative Commons GNU GPL.
Query Library
| Casey Trauer submitted this query on: June 12, 2009 4:41pm EDT | |
| TITLE | Get a list of news stories |
|---|---|
| SPORTS | ALL SPORTS |
| QUERY | SELECT doc.doc_id, doc.date_time, doc.title, dc.sportsml FROM documents doc JOIN document_contents dc ON dc.document_id = doc.id JOIN document_fixtures df ON doc.document_fixture_id = df.id JOIN affiliations_documents ad ON ad.document_id = doc.id JOIN affiliations a ON ad.affiliation_id = a.id WHERE a.affiliation_key = 'l.nfl.com' AND a.affiliation_type = 'league' AND df.fixture_key = 'general-news' ORDER BY doc.date_time DESC LIMIT 0,10; |
| DESCRIPTION | This query returns a list of the latest 10 NFL news stories. |
| DBs TESTED | MySQL |
| ADDITIONAL NOTES | |
Comments
on November 19, 2009 2:37pm EST Casey Trauer wrote:
Metadata: DB Tested: MySQL
Query:
SELECT doc.doc_id, doc.date_time, doc.title, dc.sportsml
FROM documents doc
JOIN document_contents dc ON dc.document_id = doc.id
JOIN document_fixtures df ON doc.document_fixture_id = df.id
WHERE df.fixture_key = 'general-news'
AND doc.priority = 'high'
ORDER BY doc.date_time DESC
LIMIT 0,10;
Query:
SELECT doc.doc_id, doc.date_time, doc.title, dc.sportsml
FROM documents doc
JOIN document_contents dc ON dc.document_id = doc.id
JOIN document_fixtures df ON doc.document_fixture_id = df.id
WHERE df.fixture_key = 'general-news'
AND doc.priority = 'high'
ORDER BY doc.date_time DESC
LIMIT 0,10;
"This query returns a list of the "top headlines" regardless of sport. They key column is documents.priority. A "top headline" from the Sports Network will have a value of documents.priority='high'."
on November 19, 2009 2:42pm EST Casey Trauer wrote:
Metadata: DB Tested: MS SQL
Query:
SELECT doc.doc_id, doc.date_time, doc.title, dc.sportsml
FROM documents doc
JOIN document_contents dc ON dc.document_id = doc.id
JOIN document_fixtures df ON doc.document_fixture_id = df.id
WHERE df.fixture_key = 'general-news'
AND doc.priority = 'high'
GROUP BY doc.revision_id
ORDER BY doc.date_time DESC
LIMIT 0,10;
Query:
SELECT doc.doc_id, doc.date_time, doc.title, dc.sportsml
FROM documents doc
JOIN document_contents dc ON dc.document_id = doc.id
JOIN document_fixtures df ON doc.document_fixture_id = df.id
WHERE df.fixture_key = 'general-news'
AND doc.priority = 'high'
GROUP BY doc.revision_id
ORDER BY doc.date_time DESC
LIMIT 0,10;
"This revised query modifies the "top headlines" query by ensuring it returns only one row per story. Without the clause GROUP BY doc.revision_id, you may get several revisions of the same news article returned in your query. This should return only the latest.."