Post reply

Ranking in each other

  • February 20, 2012 at 8:47 am

    #253323

    I have a table

    X Y

    ---- -----

    A B

    A B

    A C

    A C

    A C

    Here are 2 AB and 3 AC

    And I want to get an output like this:

    X Y SEQ

    ---- ---- -----

    A B 1

    A B 2

    A C 1

    A C 2

    A C 3

    How do i achive this with one nested or CTE SQL?

  • February 20, 2012 at 8:51 am

    #1449289

    BEGIN TRAN

    --Sample data

    SELECT X,Y

    INTO yourTable

    FROM (VALUES('A', 'B'),('A', 'B'),('A', 'C'),('A', 'C'),('A', 'C'))a(X,Y)

    --Solution

    SELECT X,Y,

    ROW_NUMBER() OVER(PARTITION BY X,Y ORDER BY X,Y) AS SEQ

    FROM yourTable

    ROLLBACK

    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • February 20, 2012 at 8:51 am

    #1449290

    create table #temp (x char(1), y char(1))

    insert into #temp values ('a','b')

    insert into #temp values ('a','b')

    insert into #temp values ('a','c')

    insert into #temp values ('a','c')

    insert into #temp values ('a','c')

    select row_number() over(partition by x,y order by x,y) as Seq, X, Y from #temp

  • February 20, 2012 at 8:52 am

    #1449291

    Cadavre (2/20/2012)

    BEGIN TRAN

    --Sample data

    SELECT X,Y

    INTO yourTable

    FROM (VALUES('A', 'B'),('A', 'B'),('A', 'C'),('A', 'C'),('A', 'C'))a(X,Y)

    --Solution

    SELECT X,Y,

    ROW_NUMBER() OVER(PARTITION BY X,Y ORDER BY X,Y) AS SEQ

    FROM yourTable

    ROLLBACK

    damn that was fast

  • February 20, 2012 at 9:25 am

    #1449308

    Thanks a lot

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply