locked
add a column for row number in descending order base on number of rows in table RRS feed

  • Question

  • Hi,

    I having below sample table,

    DECLARE @TBL TABLE (s_Name VARCHAR(10), s_Age INT)
    INSERT INTO @TBL VALUES ('S-1', 12)
    INSERT INTO @TBL VALUES ('S-2', 13)
    INSERT INTO @TBL VALUES ('S-3', 14)
    INSERT INTO @TBL VALUES ('S-4', 15)
    SELECT * FROM @TBL

    I m looking for a SELECT statement for below output (here total number of rows is 4) if total number of rows is 5 then 5 row with index starts at 5,

    Index s_Name s_Age

     4     s-1              12

     3                         s-2              13

     2                         s-3              14

     1                         s-4               15

    Wednesday, April 17, 2013 4:15 PM

Answers

  • DECLARE @TBL TABLE (s_Name VARCHAR(10), s_Age INT)
    INSERT INTO @TBL VALUES ('S-1', 12)
    INSERT INTO @TBL VALUES ('S-2', 13)
    INSERT INTO @TBL VALUES ('S-3', 14)
    INSERT INTO @TBL VALUES ('S-4', 15)
    SELECT * FROM @TBL
    
    
    
    select * from 
    (select row_number() over(order by age desc) index,* from @TBL) temp
    order by index desc


    Regards, Dineshkumar,
    Please 'Mark as Answer' if my post answers your question and 'Vote as Helpful' if it helps you

    Dineshkumar's BI Blog


    Wednesday, April 17, 2013 4:25 PM

All replies

  • looking for below output,

    Wednesday, April 17, 2013 4:19 PM
  • DECLARE @TBL TABLE (s_Name VARCHAR(10), s_Age INT)
    INSERT INTO @TBL VALUES ('S-1', 12)
    INSERT INTO @TBL VALUES ('S-2', 13)
    INSERT INTO @TBL VALUES ('S-3', 14)
    INSERT INTO @TBL VALUES ('S-4', 15)
    SELECT * FROM @TBL
    
    
    
    select * from 
    (select row_number() over(order by age desc) index,* from @TBL) temp
    order by index desc


    Regards, Dineshkumar,
    Please 'Mark as Answer' if my post answers your question and 'Vote as Helpful' if it helps you

    Dineshkumar's BI Blog


    Wednesday, April 17, 2013 4:25 PM