I think the most difficult ( if not impossible with just using mysql query) is the part where you apply your logic as to the "most relevant" keyword. You need to be able to have a mechanism of telling the query which keyword is the most relevant, ie, human logic says, the one that matches the search string exactly, the next most relevant is the search string that has the least deviation from the keyword etc. I'm almost convinced this cannot be done with one or even multiple queries, and I would personally use something like PHP to apply that kind of logic on the result set.
try the following: select distinct keywords, bid from ppc_keywords where keywords like "%job%" group by keywords order by bid; This should return the result grouped by the keyword ( in alphabetical order I think) and the order sorted by the highest bid, but as you can see, ajob and ajobs will be reported BEFORE job. But once you have this result, you can use PHP or PERL or the language of choice to sort it by relevance. Again, I stand to be corrected on whether mysql can return this kind of result on it's own, and I would certainly be very interested to see this done. With the advent of PHP5 with subselects and stored procedures, it might be more feasible? On Wed, 2003-09-17 at 14:51, Johan Potgieter wrote: > It's not that simple as you say. I want the most relevant keywords at the > top but it must be ordered by bid amount. > > The output I want must be: > > +----+---------+---------+----------+------+----------------+ > | id | link_id | user_id | keywords | bid | bid_time | > +----+---------+---------+----------+------+----------------+ > | 41 | 2 | 3 | job | 0.05 | 20030916144655 | > | 20 | 4 | 3 | job | 0.01 | 20030916130423 | > | 46 | 1 | 5 | job | 0.01 | 20030917134229 | > | 42 | 2 | 3 | ajob | 0.06 | 20030917124722 | > | 48 | 1 | 5 | ajob | 0.01 | 20030917134240 | > | 43 | 2 | 3 | ajobs | 0.01 | 20030917120650 | > | 49 | 1 | 5 | ajobs | 0.01 | 20030917134245 | > | 40 | 2 | 3 | JOBS | 0.02 | 20030916144512 | > | 23 | 4 | 3 | jobs | 0.01 | 20030916130537 | > | 47 | 1 | 5 | jobs | 0.01 | 20030917134236 | > +----+---------+---------+----------+------+----------------+ > > I hope it makes more sense. > > The idea is to return keywords that are exactly what the user supplied but > also keywords that closely resemble what was entered. All those results > must be grouped by keyword relevance and second by bid amount. > > Johan > > > > At 02:25 PM 2003/09/17 +0200, you wrote: > >Not sure I understand what you need? > >Can you create a dummy output table of what you would like your result > >to look like? > > > >At first glance, from reading your question, it sounds like you simply > >want this: > > > >select * from ppc_keywords where keywords like "%job%" [group by > >keywords] order by bid; > > > >where the group by keywords is kinda optional for this query, but it > >can't be that simple right? ;) > > > > > > > >On Wed, 2003-09-17 at 13:31, Johan Potgieter wrote: > > > 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 > > > > > > Johan Potgieter > > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
