Requiere TSQL Query logic help

Question

Hi All,

Can any one help to following logic.

I have one table contains millions of records with columns c1,c2,rank . it looks like following.

C1 c2 rank expected_output

22 331 1 1

22 331 2 1

44 222 1 1

55 222 2 2

66 222 3 3

77 222 4 4

33 444 1 1

33 444 2 1

33 444 3 1

Could you please some one help me on this will be more helpful.

Thanks in advance.

Answer 1

If you want to group the records based on the columns c1 and c2 and assign a unique rank to each group, you can use the following T-SQL query:

Copy code
WITH CTE_Rank AS (
    SELECT c1, c2, ROW_NUMBER() OVER (PARTITION BY c1, c2 ORDER BY c1) as rank
    FROM your_table
)
SELECT c1, c2, rank, DENSE_RANK() OVER (ORDER BY c1, c2) as expected_output
FROM CTE_Rank
This query uses a Common Table Expression (CTE) named "CTE_Rank" to assign a unique rank to each group of records based on the values of columns "c1" and "c2". The PARTITION BY clause is used to group the records based on the values of columns "c1" and "c2", and the ORDER BY clause is used to order the groups based on the values of columns "c1" and "c2". The SELECT statement then retrieves the columns "c1", "c2", "rank" and a new column "expected

Answer 2

If you want to group the records based on the columns c1 and c2 and assign a unique rank to each group, you can use the following T-SQL query:

Copy code
WITH CTE_Rank AS (
    SELECT c1, c2, ROW_NUMBER() OVER (PARTITION BY c1, c2 ORDER BY c1) as rank
    FROM your_table
)
SELECT c1, c2, rank, DENSE_RANK() OVER (ORDER BY c1, c2) as expected_output
FROM CTE_Rank
This query uses a Common Table Expression (CTE) named "CTE_Rank" to assign a unique rank to each group of records based on the values of columns "c1" and "c2". The PARTITION BY clause is used to group the records based on the values of columns "c1" and "c2", and the ORDER BY clause is used to order the groups based on the values of columns "c1" and "c2". The SELECT statement then retrieves the columns "c1", "c2", "rank" and a new column "expected

Answer 3

Maybe the next query corresponds to your new explanation:

select *, dense_rank() over (partition by c2 order by c1) expected_output
from MyTable
order by c1, [rank]

Answer 4

Hi All ,

Thanks for the response. It is resolved now.

Thanks