CREATE TABLE #tmp (id INT, val1 INT);

INSERT INTO #tmp (val1) 
SELECT 11 UNION ALL
SELECT 11 UNION ALL
SELECT 20 UNION ALL
SELECT 30 UNION ALL
SELECT 30 UNION ALL
SELECT 45 ;

WITH cte AS
(
   SELECT *,DENSE_RANK() OVER (ORDER BY val1) AS RN
   FROM #tmp
)
UPDATE cte SET id = rn;

SELECT * FROM #tmp;

DROP TABLE #tmp;

quote:

---

Originally posted by sunitabeck

Here is an example of how you can do it. If it doesn't do what you are trying to do, post the table DDL and some sample data?

---

 select dense_rank () over (order by Policynumber) as ID
	  ,[PolicyNumber]
      ,[ClientId]
      ,[InsuredName]
     FROM policy
Order by insuredname

select dense_rank () over (order by [InsuredName],Policynumber) as ID

quote:

---

Originally posted by sunitabeck

Add InsuredName also to your dense_rank sorting:

select dense_rank () over (order by [InsuredName],Policynumber) as ID

---

InsuredName PolicyNumber ClientID
Company A   00001        1
Company A   00002        1 (because the InsuredName is the same)
Company B   00001        1 (Because it has the same Policy Number 
                            as the one before it.)
Company C   00003        2 (Because neither of the columns are the 
                            same.)

InsuredName PolicyNumber ClientID
Company A   00001        1
Company A   00002        2 (because the InsuredName is the same,
                            but the PolicyNumber is different)
Company B   00001        3 (Because the name is different)
Company C   00003        4 

POLICY# INSUREDNAME
------- -----------
0700001 SOMETHING
0700001 SOMETHINGELSE
0700002 SOMETHINGELSE
0700002 YETAGAINSOMETHINGELSE
0700003 YETAGAINSOMETHINGELSE

quote:

---

Originally posted by sunitabeck

When you add both columns to the order by clause, each unique combination of the two columns is assigned a rank. But it looks like what you want is to have a rank if either of the columns are the same. That seems to be not very well-defined problem. For example, in the following example, what should the client ids be?

POLICY# INSUREDNAME
------- -----------
0700001 SOMETHING
0700001 SOMETHINGELSE
0700002 SOMETHINGELSE
0700002 YETAGAINSOMETHINGELSE
0700003 YETAGAINSOMETHINGELSE

---

POLICY# INSUREDNAME           ClientID
------- -----------           --------
0700001 SOMETHING             1
0700001 SOMETHINGELSE         1
0700002 SOMETHINGELSE         1
0700002 YETAGAINSOMETHINGELSE 1
0700003 YETAGAINSOMETHINGELSE 1

quote:

---

Originally posted by sunitabeck

I don't know of an easy way to do this in SQL without resorting to while loops or something similar that is unsightly.

One possibility is that you separate them out into two groups, one where there is one-to-many relationship from Policy Number to Insured Name and none the other way, and the second group exactly the opposite. Assign numbers to those. Then examine whatever is left out to see what needs to be done.

---

quote:

---

Originally posted by Japboix1


Hmm, so it's just a really messy situation? How exactly do you do while loops? I know how to do while loops in C, so is it similar to that?

---