0

I have a query for searching most relevant items like this:

select *,((case when title like 'mykeyword' then 3 else 0 end)+
(case when body like 'myKeyword' then 3 else 0 end)) 
as relevance from content 
order by relevance,id desc

However I want to give weight to ID so the most recent items have most relevance score. How Can I set this weight rather than "order by ID desc"?

Edit: My ID Column is auto increment and primary key where some ID may be deleted. So I Can not rely on a linear increment and I can not write a mathematical formula to give normalized weight to ID itself.

Ali Sheikhpour
  • 10,475
  • 5
  • 41
  • 82
  • How do you define how recent an item is? Is it the case that if id1 > id2 then id1 is more recent? or do you have some date column for that? – Lucia Pasarin Sep 15 '15 at 20:49
  • My ID Column is auto increment and primary key where some ID may be deleted. So I Can not rely on a linear increment. – Ali Sheikhpour Sep 16 '15 at 01:09
  • 1
    Can you show some data sample and the desired output from it? I don't understand why not rely on the ID if it is deleted then it will not came in the results so why botter? the only way you can not rely on an autoincrement (for relevance in time) is if you update something on a registry and need to know the time when it happens then you need to have a date column and update it every time you make a change. – Jorge Campos Sep 16 '15 at 01:21
  • I can not write a mathematical formula to give weight to ID because of deleted ID s. for example adding (ID^(0.5)) , adds a big number to relevance when 2000 ID are deleted between 100 and 2100. – Ali Sheikhpour Sep 17 '15 at 03:53

0 Answers0