how to count the column and show its count in another column

Question

0.00/5 (No votes)

See more:

Item_groupDetail_PK ItemGroup_PK Sortorder
46737 NULL NULL
49292 NULL NULL
40550 3 NULL
38036 4 NULL
15125 4 NULL
58310 4 NULL
61653 5 NULL
13250 5 NULL
10821 5 NULL
53087 6 NULL
25301 6 NULL
36975 7 NULL
35759 8 NULL
55940 8 NULL
8205 8 NULL
32834 8 NULL
26404 8 NULL
29371 8 NULL
49478 8 NULL
19169 8 NULL
20911 9 NULL
21167 9 NULL
21235 9 NULL

Hello All I have a small table as shown above i want to just see the ever entry in my column and keep it count in sortorder how wil i do it?? for example in above table for 3 in sortorder it shouls show 1 then for 4 it should first have 1 then 2,3,4 in sortorder. i want the sortorder column to have the count of data which is shown in 2nd column . can any one help thanks in advance

Posted 17-Dec-12 20:45pm

syedmas

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)

__TR__ · Accepted Answer · 2012-12-17T20:58:00

Try

SQL

SELECT ROW_NUMBER() OVER (PARTITION BY ItemGroup_PK ORDER BY Item_groupDetail_PK) AS SortOrder, * FROM YourTableName

Edit
Sample approach to update sortorder

SQL

CREATE TABLE #Sample
(
Item_groupDetail_PK INT,
ItemGroup_PK INT,
Sortorder INT
)

INSERT INTO #Sample
SELECT 46737, NULL, NULL UNION ALL
SELECT 49292, NULL, NULL UNION ALL
SELECT 40550, 3, NULL UNION ALL
SELECT 38036, 4, NULL UNION ALL
SELECT 15125, 4, NULL UNION ALL
SELECT 58310, 4, NULL UNION ALL
SELECT 61653, 5, NULL UNION ALL
SELECT 13250, 5, NULL UNION ALL
SELECT 10821, 5, NULL UNION ALL
SELECT 53087, 6, NULL UNION ALL
SELECT 25301, 6, NULL UNION ALL
SELECT 36975, 7, NULL UNION ALL
SELECT 35759, 8, NULL UNION ALL
SELECT 55940, 8, NULL UNION ALL
SELECT 8205, 8, NULL UNION ALL
SELECT 32834, 8, NULL UNION ALL
SELECT 26404, 8, NULL UNION ALL
SELECT 29371, 8, NULL UNION ALL
SELECT 49478, 8, NULL UNION ALL
SELECT 19169, 8, NULL UNION ALL
SELECT 20911, 9, NULL UNION ALL
SELECT 21167, 9, NULL UNION ALL
SELECT 21235, 9, NULL

--Before update
SELECT * FROM #Sample

UPDATE #Sample
SET Sortorder = T.NewSortOrder
FROM
#Sample S
INNER JOIN
(
	SELECT ROW_NUMBER() OVER (PARTITION BY ItemGroup_PK ORDER BY Item_groupDetail_PK) AS NewSortOrder, * FROM #Sample
) T ON S.Item_groupDetail_PK = T.Item_groupDetail_PK

--After update
SELECT * FROM #Sample


DROP TABLE #Sample