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

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    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

  • Question
    Sign in to vote
    1
    Sign in to vote 
    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

  • Question
    Sign in to vote
    0
    Sign in to vote

    looking for below output,

    Wednesday, April 17, 2013 4:19 PM
  • Question
    Sign in to vote
    1
    Sign in to vote 
    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