Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Retrieving multiple TOP 1s

Posted on 2003-02-20
8
Medium Priority
?
290 Views
Last Modified: 2013-12-16
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.


<cfquery name="wsQuery">
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'
</cfquery>
0
Comment
Question by:andrewshka
8 Comments
 
LVL 32

Expert Comment

by:Brendt Hess
ID: 7990455
Question:  How can I tell what the latest stat is from your table?  Assuming a field (call it Latest) that would let me know this (could be a date, a counter, etc), try this:

SELECT      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'
AND     docs.Latest = (Select Max(d2.Latest) From docs d2 WHERE d2.doc_id = docs.doc_id)
0
 
LVL 6

Expert Comment

by:rajesh009
ID: 7993186
Hello ,

Try this:

cfquery name="wsQuery">
SELECT 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.doc_status = 'A' and
docs.doc_id = (Select max(A.doc_id) from docs A where
A.template_id = docs.template_id)
</cfquery>

Thanks,
Rajesh.
0
 
LVL 1

Expert Comment

by:johan_brohn
ID: 7993257
SELECT D.doc_id, D.keywords, D.doc_title, T.template_file
FROM (
   --The record with highest doc_id
   --grouped by template_id
   SELECT doc_id = Max(doc_id)
   FROM Docs
   WHERE doc_status = 'A'
   GROUP BY template_id
) AS HighestIdLatest
JOIN Docs AS D ON HighestIdLatest.doc_id = D.doc_id
JOIN Temps T ON D.template_id = T.template_Id
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
LVL 6

Expert Comment

by:rajesh009
ID: 7997830
Hello Johan brohn ,

     Yours is just the same as mine...you have only put it in a different way in the sense you are selecting the maximum in a derived table...which is of no consequence
even performancewise...as you are using both the max function and a group by clause there...I feel a correllated subquery may be much better off...

Thanks,
Rajesh.


 
0
 
LVL 1

Accepted Solution

by:
johan_brohn earned 800 total points
ID: 7998670
Rajesh ...

They are not same ... check the query plans.

If there are 20000 records in Docs table your query will perform 20000 lookups. I will perform one.

My example is the same as:

SELECT doc_id = Max(doc_id)
INTO #TempTable
FROM Docs
WHERE doc_status = 'A'
GROUP BY template_id

SELECT D.doc_id, D.keywords, D.doc_title, T.template_file
FROM #TempTable AS HighestIdLatest
JOIN Docs AS D ON HighestIdLatest.doc_id = D.doc_id
JOIN Temps T ON D.template_id = T.template_Id
0
 
LVL 1

Expert Comment

by:KatanaCS
ID: 8012499
Suggestion #1 -
Store the code to generate the resultset in a view.  This will allow the server to have it precompiled, which is faster than any ad hoc query, even if it's written the same way.

Suggestion #2 -
When using TOP to return the most recent record, I usually ORDER my result by the key value DESCENDING.  Presuming the key value is indexed (hopefully clustered), this is a very fast way to retrieve.  However, there were some issues with TOP and ORDER BY with SQL2K up through SP2; there's a post-SP2 hotfix that corrects them, which SP3 includes as well.  Also, indexes on the criteria values will help improve performance.

Other Idea:
If you choose to create the view, you can UNION the results from the 10 separate queries (keeping some sort of sport_id value so the front end can ID them) to return one resultset, and have ColdFusion use that for display.  This will degrade the full performance slightly, but a one-shot db call for all the data is almost always faster than 10 single db calls.

Regards,
KatanaCS
0
 
LVL 3

Expert Comment

by:hakyemez
ID: 8020966
SELECT    d2.doc_id,
          d2.keywords,
          d2.doc_title,
          x.template_file
FROM      docs as d2,
   (SELECT    t.template_id,
              MAX(t.template_file) as template_file,
              MAX(d.doc_id) as doc_id
    FROM      docs as d ,temps as t
    WHERE     d.doc_status = 'A' AND
              d.template_id = t.template_id
    GROUP BY  t.template_id) as x
WHERE     d2.template_id = x.template_id and
          d2.doc_id = x.doc_id
0
 
LVL 1

Author Comment

by:andrewshka
ID: 8043885
Thanks to everyone who contributed to this discussion.  I've been buried deep in a new site build and was unable to give feedback before now.  There's clearly more than one way to solve this, and Johan's solution seems to work the best as far as speed and bandwidth usage.  Again, thanks to all.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
The purpose of this video is to demonstrate how to set up the permalinks on a WordPress Website. This will be demonstrated using a Windows 8 PC. Go to your WordPress login page. This will look like the following: mywebsite.com/wp-login.php : Go t…
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

579 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question