query for TOP 12

[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!

 

[JohnFol]

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

 

[shank]

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.

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

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!

 

[LeAnne]

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;

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