MySQL Query help needed

Posted: Wed Sep 17, 2003 11:43 am (20 years, 7 months ago)

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

Team Member Joined: 06 Jan 2002 Posts: 2564

Are you looking for something like

Code:

SELECT * from ppc_keywords WHERE keywords LIKE "%job%" GROUP BY keywords ORDER BY bid

Sorry if that's not what you wanted Sad

WebHelper Joined: 01 Mar 2003 Posts: 69 Location: Texas

If you want items grouped with the max bid amount, you need to use a Max() and a GROUP BY clause. Something like...

Code:

Select keyword, max(bid_amount)
from table
where keyword like '%job%'
group by keyword

Dave