Ranking with Dates

[paulman]

I'm currently using LIKEP for searching several fields. I would also like for the search to take into account a date field. Meaning, records with a more recent date would result in a higher rank value. Is this possible in Texis?

[John]

It is possible with Texis. It is useful to have a compound index including the date field, and you would then need to work out the weight you want to give the date, for example you could say:

... ORDER BY $rank + (DateField/86400) desc;

so that every day is worth 1 rank value.

[paulman]

My understanding is that setting 'likeprows' to a nonzero number will return the highest ranked records. So if this is set to 50, the SQL statement will return the 50 highest ranked records. Will the above order work in conjunction with 'likeprows'? Or will it take the those 50 highest ranked records and then change the order based on the date?

[John]

It will take the 50 highest ranked records, and then reorder those with the ORDER BY.

[dave49]

Regarding...

ORDER BY $rank + (DateField/86400) desc;

Can this be done with the 4.0 Copmmercial Webinator product or is a full Texis license required?

[John]

That can be done with either a Webinator or Texis license.

[Josh L]

I was trying to sort by the date type solely without much luck. Still not sure why, but I did find a solution.

Instead of weighting the date by combining it with $rank, if you convert the date to an number you can successfully order by it. The easiest way to do this, from what I found is

ORDER BY (1 + DateField) desc

Be sure to put the numeral first. (Datefield + 1) will increase the date by one but it will remain as a date type and sorting doesn't work.

[mark]

If you don't care about rank use like instead of likep and use a normal "order by DateField".