Use max column value in order by

Question

I'm trying to order a table by two columns, each with a different weighting. The first is uptime, which is a value between 0 and 1 and has a weighting of 0.3. The second is votes, which is a non-negative integer and has a weighting of 0.7.

The weighting needs to be multiplied by a value between 0-1, so I'm going to get this for votes by dividing the number of votes for each row by the maximum number of votes held by any row.

This is my query so far, and it almost works:

SELECT addr
  FROM servers
  ORDER BY (0.3 * uptime) +
           (0.7 * (votes / 100)) DESC

The 100 is hard-coded and should be the maximum value of votes. Using MAX(votes) makes the query return only the record with highest number of votes. Can this be done in a single query?

Answer 1

You could use a subquery for selecting the maximum value of votes

SELECT addr
  FROM servers
  ORDER BY (0.3 * uptime) +
           (0.7 * (votes / (SELECT MAX(votes) FROM servers))) DESC

Example fiddle here.

Answer 2

Define a variable and use it:

DECLARE @maxVotes int
SELECT @maxVotes = MAX(votes) from servers

SELECT addr
  FROM servers
  ORDER BY (0.3 * uptime) +
           (0.7 * (votes / @maxVotes)) DESC

or use a subquery in the order by:

SELECT addr
  FROM servers
  ORDER BY (0.3 * uptime) +
           (0.7 * ( votes / (SELECT MAX(votes) FROM servers))) DESC