SQL Server column Sorting with 2 or more columns being sorted

user_8738288 0
Viewed 26

I have these records in my table:

 id  sequence  question   answer  letter
  1      1        1 + 1      2       B
  2      2        2 + 2      4       E
  3      3        4 + 4      8       A
  4      4        8 + 8      16      D
  5      5       16 + 16     32      C

Is there any possibilites to make my table look like this? :

 id  sequence  question   answer  letter
  1      1        1 + 1      8       A
  2      2        2 + 2      2       B
  3      3        4 + 4      32      C
  4      4        8 + 8      16      D
  5      5       16 + 16     4       E

I mean the first one is i order by sequence, but i also wanted to get the order of it in letters because if not they will be in same position as therefore the question can be answered easily, I wanted the answer to come with the letter and order by it alphabetically. Please badly need your help :(

sql-server sql
1 Answers

--DDL

CREATE TABLE test(id int,sequence int,question varchar(20),answer int, letter varchar(5) );

    INSERT INTO test VALUES(1,1,'1+1',2,'B');
    INSERT INTO test VALUES(2,2,'2+2',4,'E');
    INSERT INTO test VALUES(3,3,'4+4',8,'A');
    INSERT INTO test VALUES(4,4,'8+8',16,'D');
    INSERT INTO test VALUES(5,5,'16+16',32,'C');

--SQL

  SELECT A.id,
           A.sequence,
           A.question,
           B.answer,
           B.letter
      FROM 
    (
    SELECT id,
           sequence,
           question,
           ROW_NUMBER() OVER ( ORDER BY id ) AS rn
      FROM test
    ) A
    INNER JOIN
    (
    SELECT answer,
           letter,
           ROW_NUMBER() OVER ( ORDER BY letter ) AS rn
      FROM test
    ) B
    ON A.rn = B.rn;

SQL Fiddle :- http://sqlfiddle.com/#!6/96e09/3

user_10498930
Related