SQL Server Performance

Help on a Select

Discussion in 'General Developer Questions' started by lookaround, Jan 9, 2009.

  1. lookaround New Member

    Hi,
    I'd need help on how to write a select query. We have an Orders tables (simplifying):
    Orders:
    OrderID
    UserID
    OrderTotal
    I should get the first N Orders order by OrderTotal DESC but avoiding duplicated UserID.
    I'd like to avoid correlate subquery (for example selecting the greater Order for each User and than select the first N among those ones) for performance reason...
    Thanks in advance!
  2. Adriaan New Member

    That means you want the top N UserIDs, ordered by their maximum OrderTotal, rather than the top N OrderIDs:

    SELECT o.* FROM Orders o
    INNER JOIN
    (SELECT TOP n m.UserID, MAX(m.OrderTotal) MaxTotal FROM Orders m
    GROUP BY m.UserID ORDER BY MAX(m.OrderTotal) DESC) x
    ON o.UserID = x.UserID AND :confused:rderTotal = x.MaxTotal
    Note that you will get a "WITH TIES" result in case a UserID has multiple orders with the maximum total.
    Why the aversion to correlated subqueries?
  3. lookaround New Member

    Thanks for help!
    I simply had performance issue with correlate subqueries in the past and so if possible i try to avoid them...
    it's not a so remote possibility that a UserID has multiple orders with the same total... Is there another way to get the result?
    Thanks again!
  4. Adriaan New Member

    If performance is poor with a correlated subquery, you probably do not have the proper index(es) to support this query.
    To suppress the multiples from this type of query, you'll need to add a correlated subquery for criteria:

    WHERE :confused:rderID IN (SELECT TOP 1 f.OrderId FROM Orders f WHERE f.UserID = x.UserID AND f.OrderTotal = x.MaxTotal ORDER BY f.xxx)
    You could also use MIN(f.OrderID) with a GROUP BY expression, but TOP 1 with or without ORDER BY will be much easier to code. Without an ORDER BY, the row will be picked at random from the "duplicates". With an ORDER BY, you could for instance take the earliest one or the most recent one.
  5. lookaround New Member

    Thank you very much!

Share This Page