That TOP 2 values SQL

[milton747]

Hi folks,

I tried out the solution posted, to get top 2 values per group. It runs but I get every order for each sales rep,
not just the TOP 2.
Any thoughts why the TOP 2 filter is not working?
SQL below. (This attracted a long thread a couple of weeks back.)



--------------------------------
Schema uses 3 tables:

T_ORDERTOTALS
Order Total
CustID (FK to T_CUSTOMER)

T_CUSTOMERS
CustID
RepID (FK to T_SALESREPS)

T_SALEREPS
RepID
RepName


========================================

SELECT
O.OrderTotal,
S.repID

FROM
OrderTotals O,
customers C,
salesreps S

WHERE
O.customName = C.customName AND
C.repID = S.repID AND
OrderTotal IN
(SELECT TOP 2 OrderTotal
FROM OrderTotals
WHERE
customName = C.customName AND
C.repID = S.repID
ORDER BY OrderTotal DESC
)

ORDER BY
S.repID,
O.OrderTotal DESC


Thanks
== eom ===

 

[r937]

your table names in the schema do not match the table names in the query, but more importantly (and the source of my confusion), there's an extra join condition on a couple of customname columns that aren't in the schema -- why?

r937.com | rudy.ca

 

[Golom]

This

C.repID = S.repID

In the subquery looks a bit suspicious. Both "C" and "S" are references to tables in the outer query and that constraint is already being enforced in the outer query.

I would also cast this as INNER joins rather than cross joins as in

SELECT 
   O.OrderTotal, 
   S.repID

FROM 
   (OrderTotals O 
   INNER JOIN customers C ON O.CustomerName = C.CustomerName)
   INNER JOIN salesreps S ON C.repID = S.repID 

WHERE 
   OrderTotal IN
                    (
                     SELECT TOP 2 OrderTotal
                     FROM OrderTotals X 
                     WHERE X.CustomerName = C.CustomerName 
                     ORDER BY OrderTotal DESC
                    )

ORDER BY 
   S.repID, 
   O.OrderTotal DESC

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]