Using Subquery to Create Difference

[infomania]

I am trying to select the remaining records after the "Top 100" records have been selected (in a sub query). I have tried NOT EXISTS without success. Here is a short form of my query put into the command box. (using CR 9)

SELECT d.cust_id, a.cust_name, sum(d.sales)
FROM detail d left outer join customers a
on d.cust_id = a.cust_id
WHERE
d.date>={?Start} and d.date<={?End}
and not exists
/*start subquery*/
(SELECT top 100 cust_id, sum(sales)
FROM detail
WHERE date>={?Start}and date<={?End}
d.cust_id = cust_id
GROUP BY cust_id
ORDER BY sum(sales) desc)
/*end subquery*/

GROUP BY d.cust.id
ORDER BY sum(d.sales) desc

When I run this I get no records returned. I tried the NOT IN syntax without success since I had more than one item in the select statement.

Any, all help will be appreciated.

 

[infomania]

This works.... contributed by another (ref below) at the SQL forum.

--Here is another method using a LEFT JOIN
SELECT d.cust_id, a.cust_name, sum(d.sales)
FROM detail d
LEFT JOIN customers a
ON d.cust_id = a.cust_id

LEFT JOIN
(SELECT top 100 cust_id
FROM detail
WHERE date>={?Start}and date<={?End}
GROUP BY cust_id
ORDER BY sum(sales) desc) top100

ON d.cust_id = top100.cust_id

WHERE d.date>={?Start} and d.date<={?End}
AND top100.Cust_ID Is Null

GROUP BY d.cust_id, a.cust_name
ORDER BY sum(d.sales) desc

Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/programmer.htm

 

[synapsevampire]

Much cleaner, Infomania - I've seen Terry's posts in SQL Server, good DB programmer...

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com