Geek asks and answers

Use maximum column value in order

I am trying to order a table in two columns, each with a different weight. The first uptime, which is a value from 0 to 1 and has a weight of 0.3. The second is votes, which is a non-negative integer and has a weight of 0.7.

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 line by the maximum number of votes belonging to any line.

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

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

100 is hardcoded and must be the maximum value votes. Using MAX(votes), the query returns only the record with the most votes. Can this be done in a single request?

+5
source share
2 answers

You can use the subquery to select the maximum value. votes

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

An example script is here .

+2
source

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 the subquery in order by:

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

More articles:


All Articles