Click here to Skip to main content
15,068,645 members
Please Sign up or sign in to vote.
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

1 solution

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
   
v2
Comments
syedmas 18-Dec-12 4:07am
   
Thank you it worked !!!!!!! Thank you so much sir!!!
__TR__ 18-Dec-12 4:07am
   
You're welcome :)
syedmas 18-Dec-12 4:13am
   
sir i have a small question can i use the above query for updation ?? i want to update my table with these count ?? if no then what would be the alternate way to update ???
__TR__ 18-Dec-12 4:24am
   
Check the updated solution. I have given a sample code where i am updating the table #Sample's sortorder column.

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




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900