[SQL] SQL Rank New Rows Higher But Only Max Every 4 Rows, Use Default Order By Otherwise

The problem is seemingly straight forward, but after messing with RANK, Partition, etc. I have not been able to find a viable solution.

We have a simple table of auction items and each has a search score. We want to give newly listed auction items a boost and show them every 4th result, even if their search score is lower. The newly listed items themselves rank by the search score (realize it's possible for a newly listed item to get slightly demoted if it has a high search score).

Continue reading...