4WebHelp
 FAQ  •  Search  •  User Groups  •  Forum Admins  •  Smilies List  •  Statistics  •  Rules   •  Login   •  Register
Toggle Navigation Menu

 MySQL Query help needed
Post New TopicReply to Topic
View Previous Topic Print this topic View Next Topic
Author Message
Amber777
Junior WebHelper
Junior WebHelper


Joined: 17 Sep 2003
Posts: 1
Location: South Africa

PostPosted: Wed Sep 17, 2003 11:43 am (14 years, 1 month ago) Reply with QuoteBack to Top

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
OfflineView User's ProfileFind all posts by Amber777Send Personal MessageVisit Poster's Website
Daniel
Team Member



Joined: 06 Jan 2002
Posts: 2564

PostPosted: Sun Sep 21, 2003 6:12 pm (14 years, 1 month ago) Reply with QuoteBack to Top

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

________________________________
Image
OfflineView User's ProfileFind all posts by DanielSend Personal Message
drathbun
WebHelper
WebHelper


Joined: 01 Mar 2003
Posts: 69
Location: Texas

PostPosted: Mon Sep 22, 2003 4:30 pm (14 years ago) Reply with QuoteBack to Top

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

________________________________
Dave
Photography Site :: Query Tools Forum :: Weekend Fun
OfflineView User's ProfileFind all posts by drathbunSend Personal MessageVisit Poster's Website
Display posts from previous:      
Post New TopicReply to Topic
View Previous Topic Print this topic View Next Topic


 Jump to:   




You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot edit your posts in this forum.
You cannot delete your posts in this forum.
You cannot vote in polls in this forum.


Page generation time: 0.050414 seconds :: 17 queries executed :: All Times are GMT
Powered by phpBB 2.0 © 2001, 2002 phpBB Group :: Based on an FI Theme