Requiere TSQL Query logic help

Srinivas K 6 Reputation points
2023-01-20T20:25:02.1566667+00:00

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.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,243 questions
{count} votes

4 answers

Sort by: Most helpful
  1. SanthiSwaroopNaikBukke-4908 595 Reputation points
    2023-01-20T20:28:17.1633333+00:00
    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
    

  2. SanthiSwaroopNaikBukke-4908 595 Reputation points
    2023-01-20T20:28:52.5266667+00:00
    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
    
    0 comments No comments

  3. Viorel 109.8K Reputation points
    2023-01-21T06:25:23.3+00:00

    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]
    
    0 comments No comments

  4. Srinivas K 6 Reputation points
    2023-01-21T21:49:05.7733333+00:00

    Hi All ,

    Thanks for the response. It is resolved now.

    Thanks