Retrieving multiple TOP 1s
Posted on 2003-02-20
I'm using sql server 2000 and Coldfusion.
I need to display the latest stats info (one line of text)for 10 different sports, all on one page. Each sport has a different template_id. I want only the latest stat from each sport, so I'm assuming I would use TOP 1. I'm trying to avoid writing 10 queries that are the same except for the template_id.
Is it possible to retrieve the TOP 1 stat for each sport in one query?
Here is what one query (using Coldfusion) looks like, feel free to optimize it if it needs it.
SELECT TOP 1 docs.doc_id, docs.keywords, docs.doc_title, temps.template_file
FROM docs INNER JOIN temps
ON docs.template_id = temps.template_id
WHERE docs.template_id = 198
AND docs.doc_status = 'A'