CODEWITHSUNDEEP
mysqlsql-serverlogic
saeed Am
saeed Am

Reputation: 23

how to sort result by giving different weight to each column?

I want to sort my result from a sql database (mysql or sql server). but there are more than one column that each column has different weight to sort for example 

SELECT F1,F2,F3 FROM mytable ORDER BY (F1*2+F2*7+F3)

but i afraid it takes too long to process because my database can have millions of records so sorting that amount of data and calculating that formula for all record could be very slow how can i do this more faster??

EDIT: these weight number is not constant

Upvotes: 2

Views: 761

Answers (2)

KumarHarsh
KumarHarsh

Reputation: 5094

@MM93,idea was good. See,there is 2 more way of achieving this.but both require little more info.

1.Pull all the desire rows without sorting.Then sort it in front end.This may be fast.

  1. write your query like this,
  2. SELECT F1,F2,F3,F1*2+F2*7+F3 as NewColumn FROM mytable

Then sort it in front end.

Upvotes: 1

Pரதீப்
Pரதீப்

Reputation: 93724

If your numbers are constant then create a computed column 

ALTER TABLE mytable
   ADD O_ComputedColumn AS (F1*2+F2*7+F3) PERSISTED

Here is some advantages of making your computed column Persisted

Query:

SELECT F1,F2,F3 
FROM mytable 
ORDER BY O_ComputedColumn

and this is for Sql Server. I never tried it give it a try

Upvotes: 2

Related Questions