?
Solved

Retrieving multiple TOP 1s

Posted on 2003-02-20
8
Medium Priority
?
286 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

 
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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
The purpose of this video is to demonstrate how to set up an RSS Feed on a WordPress Website. This will be demonstrated using a Windows 8 PC. Feedburner will be used for this demonstration. Go to your WordPress login page. This will look like the…
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

801 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