How To Order By Adding Columns Values In SQL?
-
Hiya,
I need to learn the SQL query that counts all the points from more than one column and orders the matching rows based on most points in descending order.
EXAMPLE 1:
I do a keyword search for "mobile phone tutorial apps". Note 4 words.
Sql should find all the rows that contain any of these 4 keywords.
That means, since I searched for 4 keywords, sql should even pull-up those rows that just have even 1 or 2 or 3 of these 4 keywords in them.Say, my mysql table looks like this:
id | kw1 | kw1_point | kw2 | kw2_point | kw3 | kw3_point | kw4 | kw4_point
0 | mobile | 3 | phone | 3 | apps | 2 | tutorial | 2
1 | mobile | 1 | phone | 1 | apps | 3 | tutorial | 3
2 | tutorial | 3 | apps | 3 | mobile | 2 | phone | 3
3 | mobile | 5 | tutorial | 5 | apps | 5 | usa | 5
NOTE: All 4 keywords exists on the first 3 matching rows. However, only 3 words exist in the final matching row.
And the final matching row's keywords are not in the order of my keyword search.
Here in Q1, this should not matter. Sql should ignore in which order the keywords are in each column when comparing the order of my searched keywords. All it should do, is:A). Find matching rows, regardless of how many of my searched keywords exist on each row;
B). Count the totals of each points, (count more than one column in this case), in each row; And
C) List the rows in the point's descending order.As you can see, from my example, the following are the keyword points of each row after calculating ALL their keyword points (all point columns):
id 0 = 10 point
id 1 = 8 point
id 2 = 11 point
id 3 = 20 pointSo, in this case, the SQL query should present rows in this descending order:
id 3 = 20
id 2 = 11 points
id 0 = 10 points
id 1 = 8 points
So, in this case, the SQL query should present rows in this descending order:id | kw1 | kw1_point | kw2 | kw2_point | kw3 | kw3_point | kw4 | kw4_point
3 | mobile | 5 | tutorial |
-
Hiya,
I need to learn the SQL query that counts all the points from more than one column and orders the matching rows based on most points in descending order.
EXAMPLE 1:
I do a keyword search for "mobile phone tutorial apps". Note 4 words.
Sql should find all the rows that contain any of these 4 keywords.
That means, since I searched for 4 keywords, sql should even pull-up those rows that just have even 1 or 2 or 3 of these 4 keywords in them.Say, my mysql table looks like this:
id | kw1 | kw1_point | kw2 | kw2_point | kw3 | kw3_point | kw4 | kw4_point
0 | mobile | 3 | phone | 3 | apps | 2 | tutorial | 2
1 | mobile | 1 | phone | 1 | apps | 3 | tutorial | 3
2 | tutorial | 3 | apps | 3 | mobile | 2 | phone | 3
3 | mobile | 5 | tutorial | 5 | apps | 5 | usa | 5
NOTE: All 4 keywords exists on the first 3 matching rows. However, only 3 words exist in the final matching row.
And the final matching row's keywords are not in the order of my keyword search.
Here in Q1, this should not matter. Sql should ignore in which order the keywords are in each column when comparing the order of my searched keywords. All it should do, is:A). Find matching rows, regardless of how many of my searched keywords exist on each row;
B). Count the totals of each points, (count more than one column in this case), in each row; And
C) List the rows in the point's descending order.As you can see, from my example, the following are the keyword points of each row after calculating ALL their keyword points (all point columns):
id 0 = 10 point
id 1 = 8 point
id 2 = 11 point
id 3 = 20 pointSo, in this case, the SQL query should present rows in this descending order:
id 3 = 20
id 2 = 11 points
id 0 = 10 points
id 1 = 8 points
So, in this case, the SQL query should present rows in this descending order:id | kw1 | kw1_point | kw2 | kw2_point | kw3 | kw3_point | kw4 | kw4_point
3 | mobile | 5 | tutorial |
-
I have no idea why this forum says (includes) 'MySQL', but there is a different database specific forum which this post would be better in.
Oh! Which place ? If you moderator then why you not transfer it there then ?
-
Oh! Which place ? If you moderator then why you not transfer it there then ?