Top N Per StockCode
-
Hi Clever People. I have a scenario and no matter what I try (other than cursors) I cant seem to get this working on an easy way. I have StockCodes Say 10 Detail Rows per StockCode, but I want to see say the top 5 rows per StockCode. Can anyone give me advice please on a quick and painless way or should I just go with the cursor? Kind Regards Elizma
-
Hi Clever People. I have a scenario and no matter what I try (other than cursors) I cant seem to get this working on an easy way. I have StockCodes Say 10 Detail Rows per StockCode, but I want to see say the top 5 rows per StockCode. Can anyone give me advice please on a quick and painless way or should I just go with the cursor? Kind Regards Elizma
Hope you have a table(tblStock) with a column like StockCode StockCode -------- 1 2 3 4 5 6 7 8 9 10 The query is: SELECT TOP 5 StockCode FROM TBLStockCode Hope this helps :)
Niladri Biswas
-
Hope you have a table(tblStock) with a column like StockCode StockCode -------- 1 2 3 4 5 6 7 8 9 10 The query is: SELECT TOP 5 StockCode FROM TBLStockCode Hope this helps :)
Niladri Biswas
Hi Sorry. Thanx for your supply, maybe I didn't ask the question properly. I now the plain top 5 query. My problem is: I have a master StockCode table that will contain unique StockCodes and their information. I also have a Details Table that is linked to the Master StockCode Table. Each Stock Code in the Master might have 20+ rows in the Detail table. I want to specify the Top N PER StockCode. E.g. If we use only TOP 2 StockCode1 - DetailDataRow1 DetailDataRow2 StockCode2 - DetailDataRow1 DetailDataRow2 Not maybe in this format, but I still need to get the TOP 2 rows per StockCode and not the First Top 2 Rows like what the normal solution that you suggested would do. Hope this is a bit more clear. Thanx for trying to help. Elizma
-
Hi Clever People. I have a scenario and no matter what I try (other than cursors) I cant seem to get this working on an easy way. I have StockCodes Say 10 Detail Rows per StockCode, but I want to see say the top 5 rows per StockCode. Can anyone give me advice please on a quick and painless way or should I just go with the cursor? Kind Regards Elizma
-
I am Not getting your Question pls clearly define it. use ranking Function like Dense_Rank() of SQL Server. Dinesh
Hi Thanx for your willingness to help, but I dont think there is an easy way to do what I was looking for and it is quite difficult to explain, so I decided to not waste too much time on a easy way out and to rather go for the cursor option to solve my problem. I dont think your solution would have worked either since my problem was that I have 20+ Rows Reletad to 1 StockCode. I have 1000 StockCodes. So EACH StockCode Have 20 Related Rows. Giving the Total Number of Details to 20000. So my question was. I only want to select the top 5 rows PER StockCode. I.O.W. I would now have 5000 Records returned to me. 5 Per StockCode. Hope this makes more sense. Kind Regards, Elizma
-
Hi Thanx for your willingness to help, but I dont think there is an easy way to do what I was looking for and it is quite difficult to explain, so I decided to not waste too much time on a easy way out and to rather go for the cursor option to solve my problem. I dont think your solution would have worked either since my problem was that I have 20+ Rows Reletad to 1 StockCode. I have 1000 StockCodes. So EACH StockCode Have 20 Related Rows. Giving the Total Number of Details to 20000. So my question was. I only want to select the top 5 rows PER StockCode. I.O.W. I would now have 5000 Records returned to me. 5 Per StockCode. Hope this makes more sense. Kind Regards, Elizma
Cross Apply is solution to your problem, with combination of XML, you can get a very nice output.
-
Hi Clever People. I have a scenario and no matter what I try (other than cursors) I cant seem to get this working on an easy way. I have StockCodes Say 10 Detail Rows per StockCode, but I want to see say the top 5 rows per StockCode. Can anyone give me advice please on a quick and painless way or should I just go with the cursor? Kind Regards Elizma
Do you mean suppose i have a Table stockcode item qty 1 101 10 1 102 20 1 103 20 2 104 25 2 105 20 2 106 25 2 107 26 with abc as (select Row_number() over (Partition by Stockcode order by stockcode) as [Row],StockCode,Item,Qty from Stock) select top 4 row,Item,Sum(qty) as [QTY] from abc group by Item,row result 1 101 10 1 104 25 2 102 20 2 105 20
-
Do you mean suppose i have a Table stockcode item qty 1 101 10 1 102 20 1 103 20 2 104 25 2 105 20 2 106 25 2 107 26 with abc as (select Row_number() over (Partition by Stockcode order by stockcode) as [Row],StockCode,Item,Qty from Stock) select top 4 row,Item,Sum(qty) as [QTY] from abc group by Item,row result 1 101 10 1 104 25 2 102 20 2 105 20
Hi Also not quite what I needed. If I use your example: stockcode item qty 1 101 10 1 102 20 1 103 20 2 104 25 2 105 20 2 106 25 2 107 26 The result I require is the following. (Say I want the Top 2 PER StockCode) Result: StockCode Item Qty 1 101 10 1 102 20 2 104 25 2 105 20 Thanx for trying to help. I really do appreciate it. Elizma
-
Cross Apply is solution to your problem, with combination of XML, you can get a very nice output.