Top ten list 1

[ptrifile]

There was a post today about this but i couldnt configure it to suit my needs. I have one table "tblmain" and it holds COMPANY, DATE_ORDERED, QUANTITY and ITEMNUMBER. I want to be able to query on the top ten ITEMNUMBER ordered by COMPANY. Can someone help me out with this

THanks!!!

Paul

 

[PHV]

SELECT A.COMPANY, A.DATE_ORDERED, A.QUANTITY, A.ITEMNUMBER
FROM tblmain AS A
WHERE A.QUANTITY In (SELECT TOP 10 A.QUANTITY
FROM tblmain WHERE COMPANY=A.COMPANY ORDER BY 1 DESC)
ORDER BY 1, 3 DESC;


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[DotNetGnat]

SELECT TOP 10 A.QUANTITY

should be SELECT TOP 10 QUANTITY

-DNG

 

[PHV]

Good catch DNG.

 

[ptrifile]

Hi PHV, Thank you so much for your help with this. This shows me duplicates of ITEMNUMBER in the top 10, is there a way to modify it so that it does not? so that I would get a true top ten product listing?

Thanks again its MUCH appreciated.

Paul

 

[PHV]

a true top ten product listing
Any chance you could post some input samples, actual result and expected result ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[ptrifile]

Sure thing. The following is how the data is coming out now, notice there are multiples of the same ITEMNUMBER. I would like it to get the top ten after each ITEMNUMBER is totaled so as not to have 8 of the "Top ten" be the same ITEMNUMBER. I hope I am explaining this well enough. If you have other questions please let me know and thanks again!!!!!

COMPANY DATE_ORDERE QUANTITY ITEMNUMBER
Company A 12/6/2004 71500 WL
Company A 4/8/2004 66600 BR03.00-2B
Company A 10/7/2004 53000 BR03.00-2B
Company A 6/11/2004 52000 BR03.00-2B
Company A 1/25/2005 51500 BR03.00-2B
Company A 3/5/2004 45000 BR03.00-2B
Company A 7/26/2004 36500 BR03.00-2B
Company A 2/9/2004 35000 BR03.00-2B
Company A 5/6/2004 31000 BR03.00-2B
Company A 11/3/2004 28000 FL04.60-1B
Company A 5/17/2005 50000 PDT.000-4B

 

[PHV]

I'm waiting for the expecting result ...
after each ITEMNUMBER is totaled
By company (ie ignoring DATE_ORDERED) ?
SELECT A.COMPANY, Sum(A.QUANTITY) AS Total, A.ITEMNUMBER
FROM tblmain AS A
GROUP BY A.COMPANY, A.ITEMNUMBER
HAVING Sum(A.QUANTITY) In (SELECT TOP 10 Sum(QUANTITY) AS S
FROM tblmain WHERE COMPANY=A.COMPANY GROUP BY ITEMNUMBER ORDER BY 1 DESC)
ORDER BY 1, 2 DESC;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[ptrifile]

Thanks PHV I believe that did it. I was having a tough time puting into words what i needed to do exactly.....But again I believe this is giving me the desired results! Thank you so much for your time and patience....It is GREATLY appreciated.

Thanks again!!

Paul