Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 252
  • Last Modified:

Help in a updation method sql server 2000.

This is topmost urgent.

In a procedure I have brought some data into a temp table [result_temp]
upto a certain point it holds.

select grn,sn,roll_no,grand_tot=max(grand_tot)
from result_temp group by grn,sn,roll_no
order by 4 desc,3

grn           sn                               roll_no  grand_tot  
varchar     varchar                       numeric numeric    
0437         SHARQA NAZ                3      550.00    
0439         FALAK MAHMOOD         4      550.00
0442        MUHAMMAD SAIM         6      550.00
0458        Ariba Akhter                 9      550.00
0461        Ali Qamar                        10      550.00
0478        Shaista Kiran        13      550.00
0401        AQSA KHAN        16      550.00
0405        SHAGUFTA PERVEEN     18      550.00
0477        Awais Khan                        12      549.50
0400        WALID FAROOQ        15      549.50
0412        ANS KHAN                        19      549.50
0440        RAYYAN  ALAM        5      548.50
0452        AFSHAN SHAHAB        7      548.50
0403        SHAHARYAR SAJID        17      548.00
0304        TAHA AHMED        1      547.50
0397        MUNEEZA JAVEED         14      511.50
0456        Mahanoor Haque         8      486.00
0464        Muzamil Ahmed         11      417.50

result_temp has two more columns
1) rank numeric
2) new_roll_no numeric

I want to update columns rank and new_roll_no in
a certain way. U will better understand by seeing this

grn     sn                              roll_no       grand_tot     rank     new_roll_no
varchar varchar                    numeric         numeric    numeric  numeric
0437      SHARQA NAZ         3      550.00     1           1
0439      FALAK MAHMOOD         4      550.00     1           2
0442      MUHAMMAD SAIM         6      550.00     1           3
0458      Ariba Akhter         9      550.00     1           4
0461      Ali Qamar                        10      550.00     1           5
0478      Shaista Kiran         13      550.00     1           6
0401      AQSA KHAN         16      550.00     1           7
0405      SHAGUFTA PERVEEN     18      550.00     1           8
0477      Awais Khan         12      549.50     2           9
0400      WALID FAROOQ         15      549.50     2           10
0412      ANS KHAN                         19      549.50     2           11
0440      RAYYAN  ALAM         5      548.50     3           12
0452      AFSHAN SHAHAB         7      548.50     3           13
0403      SHAHARYAR SAJID         17      548.00     4           14
0304      TAHA AHMED         1      547.50     5           15
0397      MUNEEZA JAVEED         14      511.50     6           16
0456      Mahanoor Haque         8      486.00     7           17
0464      Muzamil Ahmed         11      417.50     8           18

How to do so?


0
Mateen
Asked:
Mateen
  • 3
1 Solution
 
Kevin3NFCommented:
make the new_roll_no an identity field, or create a counter to increment by one.

For rank, is the change in rank tied to the change in grand_tot?
0
 
rafranciscoCommented:
Try this to update the Rank:

SELECT GrandTotal, IDENTITY(INT) AS Rank
INTO YourTempTable
FROM YourTable
GROUP BY GrandTotal
ORDER BY GrandTotal

UPDATE A
SET Rank = B.Rank
FROM YourTable A INNER JOIN YourTempTable B
ON A.GrandTotal = B.GrandTotal
0
 
rafranciscoCommented:
To update New_Roll_No and the Rank, together

SELECT GrandTotal, IDENTITY(INT) AS Rank
INTO YourTempTable
FROM YourTable
GROUP BY GrandTotal
ORDER BY GrandTotal DESC
GO

SELECT grn,sn,roll_no, IDENTITY(INT) AS New_Roll_No, B.Rank
FROM YourTable A INNER JOIN YourTempTable B
                                         ON A.GrandTotal = B.GrandTotal
ORDER BY GrandTotal DESC
GO
0
 
MateenAuthor Commented:
Hi rafrancisco:

I have got the idea but am unable to complete the task.

I must update the table result_temp by update command.

Look
select grn,sn,roll_no,grand_tot=max(grand_tot)
from result_temp group by grn,sn,roll_no
order by 4 desc,3

I am using max(grand_tot) that is result_temp has many rows although
grand_tot within  the group is same.

U say

SELECT grn,sn,roll_no, IDENTITY(INT) AS New_Roll_No, B.Rank
FROM YourTable A INNER JOIN YourTempTable B
                                         ON A.GrandTotal = B.GrandTotal
ORDER BY GrandTotal DESC

I know here ORDER BY GrandTotal DESC
will be replaced by ORDER BY GrandTotal DESC,roll_no asc
but I don't know how to pass Update Statement to set columns rank,new_roll_no in table result_temp





0
 
rafranciscoCommented:
select grn,sn,roll_no,grand_tot=max(grand_tot), identity(int) as new_roll_no
into result_temp2
from result_temp
group by grn,sn,roll_no
order by 4 desc,3
GO

SELECT grand_tot, IDENTITY(INT) AS Rank
INTO result_temp3
FROM result_temp2
GROUP BY grand_tot
ORDER BY grand_tot DESC
GO

UPDATE A
SET New_Roll_No = B.new_roll_no,
       Grand_Tot = C.Grand_Tot
FROM result_temp A INNER JOIN result_temp2 B ON A.grn = B.grn
                              INNER JOIN result_temp3 C ON A.grand_tot = C.grand_tot
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now