Can anybody help me sort my results in the correct way.
My Table description is:
+----------+---------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+----------------+
| id | int(11) | | PRI | NULL | auto_increment |
| link_id | int(11) | | MUL | 0 | |
| user_id | int(11) | | MUL | 0 | |
| keywords | varchar(255) | YES | | NULL | |
| bid | decimal(3,2) | | MUL | 0.01 | |
| bid_time | timestamp(14) | YES | | NULL | |
+----------+---------------+------+-----+---------+----------------+
I want to do a query that will return the most relevant keywords and they highest bid amount
my query looks like:
select * from ppc_keywords where keywords like "%job%";
The results I get is something like:
+----+---------+---------+----------+------+----------------+
| id | link_id | user_id | keywords | bid | bid_time |
+----+---------+---------+----------+------+----------------+
| 20 | 4 | 3 | job | 0.01 | 20030916130423 |
| 23 | 4 | 3 | jobs | 0.01 | 20030916130537 |
| 40 | 2 | 3 | JOBS | 0.02 | 20030916144512 |
| 41 | 2 | 3 | job | 0.05 | 20030916144655 |
| 42 | 2 | 3 | ajob | 0.06 | 20030917124722 |
| 43 | 2 | 3 | ajobs | 0.01 | 20030917120650 |
| 46 | 1 | 5 | job | 0.01 | 20030917134229 |
| 47 | 1 | 5 | jobs | 0.01 | 20030917134236 |
| 48 | 1 | 5 | ajob | 0.01 | 20030917134240 |
| 49 | 1 | 5 | ajobs | 0.01 | 20030917134245 |
+----+---------+---------+----------+------+----------------+
As you can see this is not correct. I want "job" to be grouped together with it's highest bid amount.
The following should be listed by relevance and bid.
Can anyone help refine this search, please
Regards
My Table description is:
+----------+---------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+----------------+
| id | int(11) | | PRI | NULL | auto_increment |
| link_id | int(11) | | MUL | 0 | |
| user_id | int(11) | | MUL | 0 | |
| keywords | varchar(255) | YES | | NULL | |
| bid | decimal(3,2) | | MUL | 0.01 | |
| bid_time | timestamp(14) | YES | | NULL | |
+----------+---------------+------+-----+---------+----------------+
I want to do a query that will return the most relevant keywords and they highest bid amount
my query looks like:
select * from ppc_keywords where keywords like "%job%";
The results I get is something like:
+----+---------+---------+----------+------+----------------+
| id | link_id | user_id | keywords | bid | bid_time |
+----+---------+---------+----------+------+----------------+
| 20 | 4 | 3 | job | 0.01 | 20030916130423 |
| 23 | 4 | 3 | jobs | 0.01 | 20030916130537 |
| 40 | 2 | 3 | JOBS | 0.02 | 20030916144512 |
| 41 | 2 | 3 | job | 0.05 | 20030916144655 |
| 42 | 2 | 3 | ajob | 0.06 | 20030917124722 |
| 43 | 2 | 3 | ajobs | 0.01 | 20030917120650 |
| 46 | 1 | 5 | job | 0.01 | 20030917134229 |
| 47 | 1 | 5 | jobs | 0.01 | 20030917134236 |
| 48 | 1 | 5 | ajob | 0.01 | 20030917134240 |
| 49 | 1 | 5 | ajobs | 0.01 | 20030917134245 |
+----+---------+---------+----------+------+----------------+
As you can see this is not correct. I want "job" to be grouped together with it's highest bid amount.
The following should be listed by relevance and bid.
Can anyone help refine this search, please
Regards