To fetch top 5 records for each
Item
, try this:
DECLARE @tmp TABLE (Item VARCHAR(30), customer VARCHAR(30), Amount INT)
DECLARE @i INT
DECLARE @j INT
DECLARE @k INT
DECLARE @rnd INT
SET @i = 1
SET @j = 1
SET @k = 1
WHILE (@i<10) BEGIN
SET @rnd = ((15 * RAND()) + 4)
WHILE (@k<=@rnd) BEGIN
WHILE (@j<10) BEGIN
INSERT INTO @tmp (Item, customer, Amount)
SELECT 'P' + CONVERT(VARCHAR(10),@i) AS Item, 'C' + CONVERT(VARCHAR(10),@j) AS customer, (@i* @rnd +@j -1) AS Amount
SET @j = @j+1
END
SET @k = @k + 1
SET @j = 1
END
SET @k = 1
SET @i = @i + 1
END
;WITH CTE_Items AS
(
SELECT DISTINCT Item
FROM @tmp
), CTE_Customers AS
(
SELECT t2.Item, t1.customer, t1.Amount
FROM @tmp AS t1 INNER JOIN CTE_Items AS t2 ON t1.Item = t2.Item
)
SELECT T.*
FROM (
SELECT ROW_NUMBER() OVER(PARTITION BY Item ORDER BY Item) AS RowNo, Item, customer, Amount
FROM CTE_Customers
) AS T
WHERE T.RowNo<=5
ORDER BY T.Item
Example result:
1 P1 C1 18
2 P1 C2 19
3 P1 C3 20
4 P1 C4 21
5 P1 C5 22
1 P2 C1 16
2 P2 C2 17
3 P2 C3 18
4 P2 C4 19
5 P2 C5 20
1 P3 C1 27
2 P3 C2 28
3 P3 C3 29
4 P3 C4 30
5 P3 C5 31
1 P4 C1 64
2 P4 C2 65
3 P4 C3 66
4 P4 C4 67
5 P4 C5 68
1 P5 C1 20
2 P5 C2 21
3 P5 C3 22
4 P5 C4 23
5 P5 C5 24
1 P6 C1 42
2 P6 C2 43
3 P6 C3 44
4 P6 C4 45
5 P6 C5 46
1 P7 C1 119
2 P7 C2 120
3 P7 C3 121
4 P7 C4 122
5 P7 C5 123
1 P8 C1 56
2 P8 C2 57
3 P8 C3 58
4 P8 C4 59
5 P8 C5 60
1 P9 C1 36
2 P9 C2 37
3 P9 C3 38
4 P9 C4 39
5 P9 C5 40