Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL
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 21:45pm
syedmas414

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

Try
SELECT ROW_NUMBER() OVER (PARTITION BY ItemGroup_PK ORDER BY Item_groupDetail_PK) AS SortOrder, * FROM YourTableName
 
Edit
Sample approach to update sortorder
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
  Permalink  
v2
Comments
syedmas at 18-Dec-12 4:07am
   
Thank you it worked !!!!!!! Thank you so much sir!!!
__TR__ at 18-Dec-12 4:07am
   
You're welcome :)
syedmas at 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__ at 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)

  Print Answers RSS
0 George Jonsson 215
1 Kornfeld Eliyahu Peter 169
2 OriginalGriff 120
3 PIEBALDconsult 110
4 BillWoodruff 85
0 OriginalGriff 6,165
1 DamithSL 4,658
2 Maciej Los 4,087
3 Kornfeld Eliyahu Peter 3,649
4 Sergey Alexandrovich Kryukov 3,294


Advertise | Privacy | Mobile
Web01 | 2.8.141220.1 | Last Updated 18 Dec 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100