current community

your communities

more stack exchange communities

Stack Exchange Inbox Reputation and Badges
tour help
Sign up ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 3 down vote favorite

I have a query which gets 3 books (ABC1,ABC2,ABC3) and their details. I need them in a particular order for pivoting so I have a Row number column for the same. People read none/some/all books.

Table:

StudentID|||BookName|||RowNumber
1000|||ABC1|||27
1000|||ABC2|||31
1000|||ABC3|||28
1005|||ABC2|||28
1005|||ABC3|||20
1005|||ABC1|||25
1006|||ABC1|||12
1006|||ABC2|||14
1006|||ABC3|||15

So my data would be something like the above. I need for each student, ABC1,ABC2,ABC3 should come in the order AND Rownumber to be UPDATED in descending order. So for student 1000:

ABC1||27,ABC2||28,ABC3||31

For student 1005:

ABC1||20,ABC2||25,ABC3||28

For 1006: no change (they're already in the right order).

I have all the above data into a temp table and master data into another temp table. I'm trying to write an update statement but nothing works as expected. I need some help.

share|improve this question
    
You have two SQL Server versions tagged. Which one is correct? –  Erik yesterday

1 Answer 1

up vote 6 down vote 
CREATE TABLE #t(StudentID INT, BookName VARCHAR(10), RowNumber INT);

INSERT #t(StudentID,BookName,RowNumber) VALUES
(1000,'ABC1',27),    (1000,'ABC2',31),    (1000,'ABC3',28),
(1005,'ABC2',28),    (1005,'ABC3',20),    (1005,'ABC1',25),
(1006,'ABC1',12),    (1006,'ABC2',14),    (1006,'ABC3',15);

;WITH x AS 
(
  SELECT StudentID, BookName, RowNumber, 
   rn1 = ROW_NUMBER() OVER (PARTITION BY StudentID ORDER BY BookName),
   rn2 = ROW_NUMBER() OVER (PARTITION BY StudentID ORDER BY RowNumber)
  FROM #t
)
SELECT x.StudentID, x.BookName, y.RowNumber
 FROM x INNER JOIN x AS y
  ON x.StudentID = y.StudentID
  AND x.rn1 = y.rn2
ORDER BY x.StudentID, x.rn1;
GO

DROP TABLE #t;

Results:

StudentID  BookName  RowNumber
---------  --------  ---------
1000       ABC1      27
1000       ABC2      28
1000       ABC3      31
1005       ABC1      20
1005       ABC2      25
1005       ABC3      28
1006       ABC1      12
1006       ABC2      14
1006       ABC3      15
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.