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?

Sirko · Accepted Answer · 2012-07-04T14:14:47.870

2

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.

edited Jul 04 '12 at 14:14

answered Jul 04 '12 at 14:02

Sirko

72,589
19
149
183

I think he wants to replace `100` by the `MAX(votes)`, and not `votes` for `MAX(votes)`. – aF. Jul 04 '12 at 14:13
I don't understand why, but on my db this only returns the single row with the most votes. I even tried it from phpmyadmin. Does collation matter? – Matt Jul 04 '12 at 14:26
@Matt Can you edit my fiddle to show an excerpt of your data and verify your error there? – Sirko Jul 04 '12 at 14:27
Oops dumb mistake, my real table was named with a prefix and I didn't change this in the subquery. Thanks for the help! – Matt Jul 04 '12 at 14:37

score 2 · Answer 2 · answered Jul 04 '12 at 14:04

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

Use max column value in order by

2 Answers2