Transact-SQL rank (sort) the raw votes

Question

I got stuck while developing an algorithm to sort the raw votes. The votes table looks like this:

CREATE TABLE [dbo].[Votes](
  RecordId int IDENTITY(1,1) NOT NULL,
  FirstNameId int NOT NULL,
  SecondNameId int NOT NULL,
  FirstPreferred bit NULL)

FirstNameId and SecondNameId are references to the PK of the same Names table. User votes in pairs either for first or second name and depending on that the FirstPreferred column takes either 1 (True) or 0 (false).

Please help. I really thought out nothing better than moving a cursor through the Votes table.

The Votes table is already filled with data, so I can't influence the voting process.

Votes data:

RecordId  FirstNameId  SecondNameId  FirstPreferred
   1          41            60            0
   2          49           108            0
   3         110           118            0
   4          35            41            0
   5          15           100            0
   6          12            74            1
   7          16           122            1
   8          46           118            0
   9          46           105            1
  10          93           117            0

Sample of data from Names table:

NameId    Name
  5      Nicolas
  6      Sergio
 11      Alexander
 12      Alexei
 13      Albert
 15      Anatoly
 16      Andreas

Answer 1

If I understand you right you want to count how often each name was votes for and then output the results ordered by that count? If so, you can use this:

CREATE TABLE dbo.Names(
  NameId INT,
  FullName NVARCHAR(20)
)
CREATE TABLE [dbo].[Votes](
  RecordId int IDENTITY(1,1) NOT NULL,
  FirstNameId int NOT NULL,
  SecondNameId int NOT NULL,
  FirstPreferred bit NULL)
GO
INSERT INTO dbo.Names
VALUES(1,'p1'),
      (2,'p2'),
      (3,'p3'),
      (4,'p4');


INSERT INTO dbo.Votes
VALUES (1,2,1),
       (1,3,1),
       (1,4,0),
       (2,3,1),
       (2,4,0),
       (3,4,0);

WITH VotedNames AS(       
SELECT CASE WHEN FirstPreferred = 1
            THEN FirstNameId
            ELSE SecondNameId
       END AS NameId
FROM dbo.Votes
)
SELECT NameId, COUNT(1) AS Votes
FROM VotedNames
GROUP BY NameId
ORDER BY Votes DESC;


WITH VotedNames AS(       
SELECT CASE WHEN FirstPreferred = 1
            THEN FirstNameId
            ELSE SecondNameId
       END AS NameId
FROM dbo.Votes
)
,VoteCounts AS(
SELECT NameId, COUNT(1) AS Votes
FROM VotedNames
GROUP BY NameId
)
SELECT n.*,ISNULL(v.Votes,0) AS Votes
FROM dbo.Names AS n
LEFT JOIN VoteCounts AS v
ON n.NameId = v.NameId
ORDER BY v.Votes DESC;

The first select excludes NameIds that did not get voted at all. The second select joins to the name table and includes non-votees too.