Re: Alternative for TOP

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Joe Celko (jcelko212_at_earthlink.net)
Date: 08/04/04 

Date: Wed, 04 Aug 2004 11:08:10 -0700

SELECT TOP(n) is a proprietary feature that was easy to implement in SQL
Server because of the use of a contigous storage file system under the
covers. I have a whole chapter in SQL FOR SMARTIES with standard SQL
solutions for this problem, using top salaries as the example.

The best answer given uses a subquery to establish a subset based on a
count. The idea is take each salary and build a group of other salaries
which are less than or equal to it. The groups with three or fewer rows
is what we want to see.

This query gives a columnar answer. It can be extended to other numbers
by changing @n and playing with the two comparison operators.

 SELECT DISTINCT salary
   FROM Personnel AS P1
  WHERE @n >= (SELECT COUNT(*) - 1 -- control parameter
               FROM Personnel AS P2
              WHERE P1.salary < P2.salary)

An equivalent version can also be done with a GROUP BY clause, but you
need to use a COUNT(DISTINCT x) operator to handle duplicates. Here is
that code.

 SELECT P1.salary
   FROM Personnel AS P1, Personnel AS P2
  WHERE P1.salary <= P2.salary
  GROUP BY P1.salary
 HAVING COUNT(DISTINCT P2.salary) <= @n; -- control parameter

The performance of these two approaches will vary with each SQL
implementation and with the available indexing. It is probably best to
experiment with both.

--CELKO--
 ===========================
 Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Relevant Pages

  • Re: Alternative for TOP
    ... If you are not available post a free pdf version of your book on the net. ... I have a whole chapter in SQL FOR SMARTIES with standard SQL ... using top salaries as the example. ... > by changing @n and playing with the two comparison operators. ...
    (microsoft.public.sqlserver.programming)
  • Re: Damsel in distress!
    ... I am trying to write this in SQL and your post ... >CREATE TABLE TimeCards ... > FROM Personnel AS P1, ... > Calendar AS C1 ...
    (microsoft.public.sqlserver.programming)
  • Re: Can some explain this to me?
    ... Google for a recent posting of mine on scoping rules in SQL. ... learn how to properly name data elements -- why is a personnel ... DELETE FROM Personnel -- collective name ... Do entire companies expire in your data model? ...
    (comp.databases.ms-sqlserver)
  • Re: Need query to return absence of record
    ... time I do the query. ... FROM Personnel As P LEFT JOIN TrainingHistory As T ... I put in the SQL (except I changed T,ClassName is Null to T.Classname ...
    (microsoft.public.access.queries)
  • Re: Need query to return absence of record
    ... time I do the query. ... FROM Personnel As P LEFT JOIN TrainingHistory As T ... I put in the SQL (except I changed T,ClassName is Null to T.Classname ...
    (microsoft.public.access.queries)