query for TOP 12

  • Thread starter Thread starter shank
  • Start date Start date
S

shank

I have the following query. Is there a way to only return the 12 highest
[StockReport.QtyInStock] for each group of [StockReport.Description2] ... ?
In simple terms, I need the top 12 order#s in each group, sorted by Qty.
DESC.

SELECT StockReport.OrderNo, StockReport.Description2, StockReport.QtyInStock
FROM StockReport INNER JOIN [999 OrderNumbers] ON StockReport.OrderNo = [999
OrderNumbers].OrderNo
ORDER BY StockReport.Description2, StockReport.QtyInStock DESC;

thanks!
 
Yes, use the TOP keyword, or look at the properties of the query (right
click in the top half of the qbe)
 
Thanks, but that only gives me the TOP values for the whole query. I'm
trying to get the TOP values for each group without individual queries.

JohnFol said:
Yes, use the TOP keyword, or look at the properties of the query (right
click in the top half of the qbe)


shank said:
I have the following query. Is there a way to only return the 12 highest
[StockReport.QtyInStock] for each group of [StockReport.Description2]
Click to expand...
....
?
In simple terms, I need the top 12 order#s in each group, sorted by Qty.
DESC.

SELECT StockReport.OrderNo, StockReport.Description2, StockReport.QtyInStock
FROM StockReport INNER JOIN [999 OrderNumbers] ON StockReport.OrderNo = [999
OrderNumbers].OrderNo
ORDER BY StockReport.Description2, StockReport.QtyInStock DESC;

thanks!
Click to expand...
Click to expand...
 
Hi shank,
I have the following query. Is there a way to only return the 12 highest
[StockReport.QtyInStock] for each group of [StockReport.Description2] ... ?
In simple terms, I need the top 12 order#s in each group, sorted by Qty.
DESC.

SELECT StockReport.OrderNo, StockReport.Description2, StockReport.QtyInStock
FROM StockReport INNER JOIN [999 OrderNumbers] ON StockReport.OrderNo = [999
OrderNumbers].OrderNo
ORDER BY StockReport.Description2, StockReport.QtyInStock DESC;
Click to expand...

It's a little snarky, but this can be done using a subquery. Something
like (WARNING: AIR CODE):

SELECT StockReport.OrderNo, StockReport.Description2,
StockReport.QtyInStock
FROM StockReport INNER JOIN [999 OrderNumbers] ON StockReport.OrderNo =
[999 OrderNumbers].OrderNo
WHERE (((StockReport.QtyInStock) In (SELECT TOP 12 t2.QtyInStock FROM
StockReport as t2 WHERE t2.Description2=StockReport.Description2 ORDER
BY
t2.QtyInStock DESC)))
ORDER BY StockReport.OrderNo;

Let me know if this works!

LeAnne
 
Back
Top