How can i select top customer based on product in sql server?

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

Hi all experts,
I have 1 table(tblmix_sales_header_details).this table contains of invoice info,customer info and product info.i want to display top customer based on each product.ex: in table has 50 rows and it has 4 items (P1,P2,P3,P4) and if i select customer info for P1, i'll see 15 customer and for P2 i'll see 10 customer ....
My requirement is that i want to display list of item (P1->P4) and also display customer for each item and the customer will be top by parameter. ex: i want to display top 5 customers so each item (P1->P4) will has 5 customer for each item.
So Does anybody know what query should i use?

Thanks

TONY

[EDIT #1]

Here is my example:

Item     customer    Amount
P1       C1          10
P2       C1          10
P3       C1          10
P1       C2          10
P3       C2          10
P1       C3          10
P2       C3          10

Other Item and customer...........
In this ex:P1 has 3 customers, P2 has 2 customers,P3 has 2 customers.
If i select top 3=> The result need to display

Item    Customer Amount
P1
        C1       10
        C2       10
        C3       10
P2
        C1       10
        C3       10
P3
        C1       10
        C2       10

and other Item

Thanks

TONY

Posted 7-Jul-13 0:56am

soeun tony

Updated 7-Jul-13 6:13am

Maciej Los

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Answer 1 · 2013-07-07T01:38:00

To fetch top 5 records for each Item, try this:

SQL

DECLARE @tmp TABLE (Item VARCHAR(30), customer VARCHAR(30), Amount INT)

--insert testing data 
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


--using CTE
;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

--TOTAL Count
--SELECT Item, [C1],[C2],[C3],[C4],[C5],[C6],[C7],[C8],[C9]
--FROM (
--	SELECT Item, customer
--	FROM @tmp
--	) AS DT
--PIVOT(COUNT(customer) FOR customer IN([C1],[C2],[C3],[C4],[C5],[C6],[C7],[C8],[C9])) AS PT

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