Professional Web Applications Themes

Tricky (Impossible?) - Nested Top - Microsoft SQL / MS SQL Server

I know how to solve this problem with multiple queries using a while statement but I am curious if anyone knows of a way to do it in a single select statement. To explain/demonstrate the problem I will transfer the concept to the Northwind database. Essentially, I would like to know the top 5 customers by sales amount for each country. In Nortwind the following query orders things correctly but if I add TOP 5 to it I get top five for the query not for top five for each country. USE Northwind SELECT --TOP 5 CustomerID, ShipCountry, SUM(UnitPrice * ...

  1. #1

    Default Tricky (Impossible?) - Nested Top

    I know how to solve this problem with multiple queries
    using a while statement but I am curious if anyone knows
    of a way to do it in a single select statement.

    To explain/demonstrate the problem I will transfer the
    concept to the Northwind database. Essentially, I would
    like to know the top 5 customers by sales amount for each
    country. In Nortwind the following query orders things
    correctly but if I add TOP 5 to it I get top five for the
    query not for top five for each country.

    USE Northwind

    SELECT --TOP 5
    CustomerID,
    ShipCountry,
    SUM(UnitPrice * Quantity)
    FROM
    Orders o
    INNER JOIN [Order Details] od ON o.OrderID =
    od.OrderID
    GROUP BY
    CustomerID,
    ShipCountry
    ORDER BY
    ShipCountry,
    SUM(UnitPrice * Quantity) DESC


    Archibald Guest

  2. #2

    Default Re: Tricky (Impossible?) - Nested Top

    A view simplifies the code:

    create view OrderSum
    as
    select
    o.CustomerID
    , c.City
    , sum (od.UnitPrice * od.Quantity) as Total
    from
    Customers c
    join Orders o on o.CustomerID = c.CustomerID
    join [Order Details] od on od.OrderID = o.OrderID
    group by
    o.CustomerID
    , c.City
    go

    select
    o.City
    , o.Total
    , o.CustomerID
    from
    OrderSum o
    where
    o.CustomerID in
    (
    select top 5
    i.CustomerID
    from
    OrderSum i
    where
    i.City = o.City
    order by
    i.Total desc
    )
    order by
    o.City
    , o.Total desc
    go

    drop view OrderSum


    --
    Tom

    ---------------------------------------------------------------
    Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
    SQL Server MVP
    Columnist, SQL Server Professional
    Toronto, ON Canada
    www.pinnaclepublishing.com/sql


    "Archibald" <com> wrote in message news:033e01c35b8e$32127560$gbl...
    I know how to solve this problem with multiple queries
    using a while statement but I am curious if anyone knows
    of a way to do it in a single select statement.

    To explain/demonstrate the problem I will transfer the
    concept to the Northwind database. Essentially, I would
    like to know the top 5 customers by sales amount for each
    country. In Nortwind the following query orders things
    correctly but if I add TOP 5 to it I get top five for the
    query not for top five for each country.

    USE Northwind

    SELECT --TOP 5
    CustomerID,
    ShipCountry,
    SUM(UnitPrice * Quantity)
    FROM
    Orders o
    INNER JOIN [Order Details] od ON o.OrderID =
    od.OrderID
    GROUP BY
    CustomerID,
    ShipCountry
    ORDER BY
    ShipCountry,
    SUM(UnitPrice * Quantity) DESC



    Tom Guest

  3. #3

    Default Re: Tricky (Impossible?) - Nested Top

    Not the best performing one...

    SELECT o.CustomerID, o.ShipCountry, SUM(UnitPrice * Quantity) AS "Total"
    FROM Orders o
    INNER JOIN [Order Details] od
    ON o.OrderID = od.OrderID
    WHERE o.CustomerID IN ( SELECT TOP 5 o1.CustomerID
    FROM Orders o1
    INNER JOIN [Order Details] od1
    ON o1.OrderID = od1.OrderID
    WHERE o1.ShipCountry = o.ShipCountry
    GROUP BY o1.CustomerID, o1.ShipCountry
    ORDER BY SUM(UnitPrice * Quantity) DESC )
    GROUP BY CustomerID, ShipCountry
    ORDER BY ShipCountry, Total ;

    --
    - Anith
    ( Please reply to newsgroups only )


    Anith Guest

  4. #4

    Default Re: Tricky (Impossible?) - Nested Top

    Not fast, but ANSI-SQL compliant. However, it works as TOP 5 WITH
    TIES...

    create view OrderSum
    as
    SELECT o.CustomerID, o.ShipCountry, SUM(UnitPrice * Quantity) AS "Total"
    FROM Orders o
    INNER JOIN [Order Details] od
    ON o.OrderID = od.OrderID
    GROUP BY CustomerID, ShipCountry
    go

    SELECT os.CustomerID, os.ShipCountry, os.Total
    FROM OrderSum os
    WHERE 5 >= (
    SELECT COUNT(*)
    FROM OrderSum os1
    WHERE os1.ShipCountry = os.ShipCountry
    AND os1.Total >= os.Total
    )
    ORDER BY ShipCountry, Total
    go

    drop view OrderSum

    Gert-Jan


    Archibald wrote: 
    Gert-Jan Guest

  5. #5

    Default Re: Tricky (Impossible?) - Nested Top

    Gert-Jan,

    Your query can be speeded up a hundredfold if you use an
    indexed view. No more ANSI compliance, though.

    I/O statistics without an indexed view:
    Table 'Order Details'. Scan count 5842, logical reads 11781, physical
    reads 0, read-ahead reads 0.
    Table 'Orders'. Scan count 90, logical reads 152742, physical reads 0,
    read-ahead reads 0.

    I/O as below (same query, indexed view):
    Table 'OrderSum'. Scan count 90, logical reads 180, physical reads 0,
    read-ahead reads 0.


    create view OrderSum with schemabinding
    as
    SELECT
    o.CustomerID,
    o.ShipCountry,
    COUNT_BIG(*) as countBig,
    SUM(UnitPrice * Quantity) AS Total
    FROM dbo.Orders o
    INNER JOIN dbo.[Order Details] od
    ON o.OrderID = od.OrderID
    GROUP BY o.CustomerID, o.ShipCountry
    go

    create unique clustered index OrderSum_uci on OrderSum(ShipCountry,
    CustomerID)
    go

    SELECT os.CustomerID, os.ShipCountry, os.Total
    FROM OrderSum os
    WHERE 5 >= (
    SELECT COUNT(*)
    FROM OrderSum os1
    WHERE os1.ShipCountry = os.ShipCountry
    AND os1.Total >= os.Total
    )
    ORDER BY ShipCountry, Total
    go

    drop view OrderSum

    -- Steve Kass
    -- Drew University
    -- Ref: 115AC447-FABE-495E-9E2E-5E11DFD4A861

    Gert-Jan Strik wrote:
     [/ref]

    Steve Guest

  6. #6

    Default Re: Tricky (Impossible?) - Nested Top

    That should do it :-)

    Still ANSI compliant though... I don't think an indexed view can violate
    the ANSI SQL standard, because ANSI only states the rules of SQL, not
    the implementation. ANSI SQL has no concept of indexes (or the lack of
    it).

    Gert-Jan


    Steve Kass wrote: [/ref][/ref]
    Gert-Jan Guest

  7. #7

    Default Re: Tricky (Impossible?) - Nested Top

    Gert-Jan,

    That makes sense. I was just thinking that it won't
    p according to ANSI, but it would be silly of me
    to suggest never using indexes anywhere because they
    are not compliant!

    SK

    Gert-Jan Strik wrote:
     
    >>
    >> 
    >>
    >> [/ref][/ref]

    Steve Guest

Similar Threads

  1. A Tricky One (well for me anyway)
    By DazFaz in forum Macromedia Flash Flashcom
    Replies: 0
    Last Post: November 2nd, 11:58 AM
  2. a tricky one...
    By Atmosfera in forum Macromedia Flash Data Integration
    Replies: 2
    Last Post: July 28th, 01:43 PM
  3. it's tricky!
    By ciecierega webforumsuser@macromedia.com in forum Macromedia Flash Sitedesign
    Replies: 0
    Last Post: November 10th, 03:18 PM
  4. A tricky one?
    By Jim Franklin in forum Microsoft Access
    Replies: 5
    Last Post: July 2nd, 06:52 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139