Art-Location Groups, Sql Server

Language: Layout:

CREATE TABLE sponsorships (
  sponsorshipID INT NOT NULL PRIMARY KEY IDENTITY,
  sponsorshipLocationID INT NOT NULL,
  sponsorshipArtworkID INT NOT NULL);

INSERT INTO sponsorships (sponsorshipLocationID, sponsorshipArtworkID) VALUES (1, 1);
INSERT INTO sponsorships (sponsorshipLocationID, sponsorshipArtworkID) VALUES (1, 2);
INSERT INTO sponsorships (sponsorshipLocationID, sponsorshipArtworkID) VALUES (2, 1);
INSERT INTO sponsorships (sponsorshipLocationID, sponsorshipArtworkID) VALUES (2, 2);
INSERT INTO sponsorships (sponsorshipLocationID, sponsorshipArtworkID) VALUES (3, 3);
INSERT INTO sponsorships (sponsorshipLocationID, sponsorshipArtworkID) VALUES (4, 3);
INSERT INTO sponsorships (sponsorshipLocationID, sponsorshipArtworkID) VALUES (5, 4);
INSERT INTO sponsorships (sponsorshipLocationID, sponsorshipArtworkID) VALUES (6, 1);
INSERT INTO sponsorships (sponsorshipLocationID, sponsorshipArtworkID) VALUES (7, 1);
INSERT INTO sponsorships (sponsorshipLocationID, sponsorshipArtworkID) VALUES (7, 3);

SELECT  *
FROM    sponsorships s
ORDER BY s.sponsorshipLocationID, s.sponsorshipArtworkID

-- Here's the output I want.
CREATE TABLE sponGroups (
  rank INT,
  sponsorshipID INT,
  sponsorshipLocationID INT,
  sponsorshipArtworkID INT);

INSERT INTO sponGroups VALUES (1, 1, 1, 1);
INSERT INTO sponGroups VALUES (1, 2, 1, 2);
INSERT INTO sponGroups VALUES (1, 3, 2, 1);
INSERT INTO sponGroups VALUES (1, 4, 2, 2);
INSERT INTO sponGroups VALUES (2, 5, 3, 3);
INSERT INTO sponGroups VALUES (2, 6, 4, 3);
INSERT INTO sponGroups VALUES (3, 7, 5, 4);
INSERT INTO sponGroups VALUES (4, 8, 6, 1);
INSERT INTO sponGroups VALUES (5, 9, 7, 1);
INSERT INTO sponGroups VALUES (5, 10, 7, 3);

SELECT  *
FROM    sponGroups sg
ORDER BY sg.rank, sg.sponsorshipID, sg.sponsorshipLocationID, sg.sponsorshipArtworkID

View schema
Execution time: 0,02 sec, rows selected: 20, rows affected: 20, absolute service time: 0,16 sec