Order By - Fair ordering on eccomerce site

Hi Guys,

I'm having a problem finding a solution to this, i know exactly what i
want to do but its just doing it thats the problem.

Heres the facts:
- I cant change products database in any way
- All results must be shown so no grouping

The Problem:

When searching for products i'd like to make the results fairer for
each supplier currently, our database has a degree of random ordering
but this is only due to records being removed then the empty record
being taken by another product. So the default ordering of the
database has large chucks of products from the same supplier.

Currently the ordering is done by 2 factors :
- zero priced parts are placed at the bottom of results
- products are ordered by price ASC (this can be changed from the user
end)

what i want to do is put an extra level of of ordering within price
blocks that orders the products one from each manufacturer in a
repeating pattern. Theres no problem pulling out the manufacturer
codes randomising them butthe problem im having is ordering them in a
set pattern so that each supplier has a part at the top of a price
block and there no suppliers with large chunks of products hogging all
the sales.

For example

Manufacturer manuID | Price
id2 10.00
id3 10.00
id5 10.00
id2 10.00
id3 10.00
id5 10.00

i know i can use order by field
(column_name,'field_value','field_value') but this needs to sort the
results in this repeating pattern.

Any ideas ?

On 21 Feb, 14:11, "Peter H. Coffin" <hell...@ninehells.com> wrote:
> Sure, add a "last order placed on" date column, and sort by that as
> well. The supplier you've ordered from longest ago will pop out on top.
> When you order from them, slap the current date into the column.

Good answer apart from missing the first fact:
- I cant change products database in any way

On 21 Feb, 15:19, "Peter H. Coffin" <hell...@ninehells.com> wrote:
> On 21 Feb 2007 06:53:19 -0800, Captain Paralytic wrote:
>
> > On 21 Feb, 14:11, "Peter H. Coffin" <hell...@ninehells.com> wrote:
> >> Sure, add a "last order placed on" date column, and sort by that as
> >> well. The supplier you've ordered from longest ago will pop out on top.
> >> When you order from them, slap the current date into the column.
>
> > Good answer apart from missing the first fact:
> > - I cant change products database in any way
>
> Can you add another table, even? That could be joined in, by whatever
> the vendor ID is. (If you can't do ANYTHING with the database, then the
> answer is entirely "Must be handled programmatically.")
>
> --
> 14. The hero is not entitled to a last kiss, a last cigarette, or any other
> form of last request.
> --Peter Anspach's list of things to do as an Evil Overlord

Well he said that he couldn't change he products "database". I guess
he could have meant "table", but we'll have to wait for him to come
back on that.

On Feb 21, 4:44 pm, "Captain Paralytic" <paul_laut...@yahoo.com>
wrote:
> On 21 Feb, 15:19, "Peter H. Coffin" <hell...@ninehells.com> wrote:
>
>
>
> > On 21 Feb 2007 06:53:19 -0800, Captain Paralytic wrote:
>
> > > On 21 Feb, 14:11, "Peter H. Coffin" <hell...@ninehells.com> wrote:
> > >> Sure, add a "last order placed on" date column, and sort by that as
> > >> well. The supplier you've ordered from longest ago will pop out on top.
> > >> When you order from them, slap the current date into the column.
>
> > > Good answer apart from missing the first fact:
> > > - I cant change products database in any way
>
> > Can you add another table, even? That could be joined in, by whatever
> > the vendor ID is. (If you can't do ANYTHING with the database, then the
> > answer is entirely "Must be handled programmatically.")
>
> > --
> > 14. The hero is not entitled to a last kiss, a last cigarette, or any other
> > form of last request.
> > --Peter Anspach's list of things to do as an Evil Overlord
>
> Well he said that he couldn't change he products "database". I guess
> he could have meant "table", but we'll have to wait for him to come
> back on that.

Hi Guys,

Thanks for your help ,

I think i might of over explained what i want to do. I see this as
ordering problem.

Right now when someone searches on the site they are served a pagnated
list of products order by price low to high.
What i want to do is inside a price block of say £10, say 6 suppliers
have 10 products each at £10. Order them in a repeated pattern so that
each supplier has a product listed in the top 6 for £10.

supplier 1 - product title - $10
supplier 2 - product title - $10
supplier 3 - product title - $10
supplier 4 - product title - $10
supplier 5 - product title - $10
supplier 6 - product title - $10
supplier 1 - product title - $10
supplier 2 - product title - $10
supplier 3 - product title - $10
and so on....


Getting who comes where in the pattern list is already covered but
actually getting the results to follow this pattern inside the price
blocks is the problem.

We found that some suppliers have large blocks of products in the
database and hence for price blocks they are appearing in some
instances all on the first page. Which isnt fair really to other
suppliers.

The products table i can't touch, but i can add tables and change the
supllier records no problem.

Thanks for your help,

Tom

On Feb 21, 5:05 pm, "Tom Wilson" <t...@actual-systems.com> wrote:
> On Feb 21, 4:44 pm, "Captain Paralytic" <paul_laut...@yahoo.com>
> wrote:
>
>
>
> > On 21 Feb, 15:19, "Peter H. Coffin" <hell...@ninehells.com> wrote:
>
> > > On 21 Feb 2007 06:53:19 -0800, Captain Paralytic wrote:
>
> > > > On 21 Feb, 14:11, "Peter H. Coffin" <hell...@ninehells.com> wrote:
> > > >> Sure, add a "last order placed on" date column, and sort by that as
> > > >> well. The supplier you've ordered from longest ago will pop out ontop.
> > > >> When you order from them, slap the current date into the column.
>
> > > > Good answer apart from missing the first fact:
> > > > - I cant change products database in any way
>
> > > Can you add another table, even? That could be joined in, by whatever
> > > the vendor ID is. (If you can't do ANYTHING with the database, then the
> > > answer is entirely "Must be handled programmatically.")
>
> > > --
> > > 14. The hero is not entitled to a last kiss, a last cigarette, or anyother
> > > form of last request.
> > > --Peter Anspach's list of things to do as an Evil Overlord
>
> > Well he said that he couldn't change he products "database". I guess
> > he could have meant "table", but we'll have to wait for him to come
> > back on that.
>
> Hi Guys,
>
> Thanks for your help ,
>
> I think i might of over explained what i want to do. I see this as
> ordering problem.
>
> Right now when someone searches on the site they are served a pagnated
> list of products order by price low to high.
> What i want to do is inside a price block of say £10, say 6 suppliers
> have 10 products each at £10. Order them in a repeated pattern so that
> each supplier has a product listed in the top 6 for £10.
>
> supplier 1 - product title - $10
> supplier 2 - product title - $10
> supplier 3 - product title - $10
> supplier 4 - product title - $10
> supplier 5 - product title - $10
> supplier 6 - product title - $10
> supplier 1 - product title - $10
> supplier 2 - product title - $10
> supplier 3 - product title - $10
> and so on....
>
> Getting who comes where in the pattern list is already covered but
> actually getting the results to follow this pattern inside the price
> blocks is the problem.
>
> We found that some suppliers have large blocks of products in the
> database and hence for price blocks they are appearing in some
> instances all on the first page. Which isnt fair really to other
> suppliers.
>
> The products table i can't touch, but i can add tables and change the
> supllier records no problem.
>
> Thanks for your help,
>
> Tom

It's not a perfect answer but by far the simplest way is ORDER BY
RAND(),PRICE

On Feb 21, 6:16 pm, "strawberry" <zac.ca...@gmail.com> wrote:
> On Feb 21, 5:05 pm, "Tom Wilson" <t...@actual-systems.com> wrote:
>
>
>
> > On Feb 21, 4:44 pm, "Captain Paralytic" <paul_laut...@yahoo.com>
> > wrote:
>
> > > On 21 Feb, 15:19, "Peter H. Coffin" <hell...@ninehells.com> wrote:
>
> > > > On 21 Feb 2007 06:53:19 -0800, Captain Paralytic wrote:
>
> > > > > On 21 Feb, 14:11, "Peter H. Coffin" <hell...@ninehells.com> wrote:
> > > > >> Sure, add a "last order placed on" date column, and sort by thatas
> > > > >> well. The supplier you've ordered from longest ago will pop out on top.
> > > > >> When you order from them, slap the current date into the column.
>
> > > > > Good answer apart from missing the first fact:
> > > > > - I cant change products database in any way
>
> > > > Can you add another table, even? That could be joined in, by whatever
> > > > the vendor ID is. (If you can't do ANYTHING with the database, thenthe
> > > > answer is entirely "Must be handled programmatically.")
>
> > > > --
> > > > 14. The hero is not entitled to a last kiss, a last cigarette, or any other
> > > > form of last request.
> > > > --Peter Anspach's list of things to do as an Evil Overlord
>
> > > Well he said that he couldn't change he products "database". I guess
> > > he could have meant "table", but we'll have to wait for him to come
> > > back on that.
>
> > Hi Guys,
>
> > Thanks for your help ,
>
> > I think i might of over explained what i want to do. I see this as
> > ordering problem.
>
> > Right now when someone searches on the site they are served a pagnated
> > list of products order by price low to high.
> > What i want to do is inside a price block of say £10, say 6 suppliers
> > have 10 products each at £10. Order them in a repeated pattern so that
> > each supplier has a product listed in the top 6 for £10.
>
> > supplier 1 - product title - $10
> > supplier 2 - product title - $10
> > supplier 3 - product title - $10
> > supplier 4 - product title - $10
> > supplier 5 - product title - $10
> > supplier 6 - product title - $10
> > supplier 1 - product title - $10
> > supplier 2 - product title - $10
> > supplier 3 - product title - $10
> > and so on....
>
> > Getting who comes where in the pattern list is already covered but
> > actually getting the results to follow this pattern inside the price
> > blocks is the problem.
>
> > We found that some suppliers have large blocks of products in the
> > database and hence for price blocks they are appearing in some
> > instances all on the first page. Which isnt fair really to other
> > suppliers.
>
> > The products table i can't touch, but i can add tables and change the
> > supllier records no problem.
>
> > Thanks for your help,
>
> > Tom
>
> It's not a perfect answer but by far the simplest way is ORDER BY
> RAND(),PRICE

I meant ORDER BY PRICE, RAND()

On Feb 21, 6:19 pm, "strawberry" <zac.ca...@gmail.com> wrote:
> On Feb 21, 6:16 pm, "strawberry" <zac.ca...@gmail.com> wrote:
>
>
>
> > On Feb 21, 5:05 pm, "Tom Wilson" <t...@actual-systems.com> wrote:
>
> > > On Feb 21, 4:44 pm, "Captain Paralytic" <paul_laut...@yahoo.com>
> > > wrote:
>
> > > > On 21 Feb, 15:19, "Peter H. Coffin" <hell...@ninehells.com> wrote:
>
> > > > > On 21 Feb 2007 06:53:19 -0800, Captain Paralytic wrote:
>
> > > > > > On 21 Feb, 14:11, "Peter H. Coffin" <hell...@ninehells.com> wrote:
> > > > > >> Sure, add a "last order placed on" date column, and sort by that as
> > > > > >> well. The supplier you've ordered from longest ago will pop out on top.
> > > > > >> When you order from them, slap the current date into the column.
>
> > > > > > Good answer apart from missing the first fact:
> > > > > > - I cant change products database in any way
>
> > > > > Can you add another table, even? That could be joined in, by whatever
> > > > > the vendor ID is. (If you can't do ANYTHING with the database, then the
> > > > > answer is entirely "Must be handled programmatically.")
>
> > > > > --
> > > > > 14. The hero is not entitled to a last kiss, a last cigarette, orany other
> > > > > form of last request.
> > > > > --Peter Anspach's list of things to do as an EvilOverlord
>
> > > > Well he said that he couldn't change he products "database". I guess
> > > > he could have meant "table", but we'll have to wait for him to come
> > > > back on that.
>
> > > Hi Guys,
>
> > > Thanks for your help ,
>
> > > I think i might of over explained what i want to do. I see this as
> > > ordering problem.
>
> > > Right now when someone searches on the site they are served a pagnated
> > > list of products order by price low to high.
> > > What i want to do is inside a price block of say £10, say 6 suppliers
> > > have 10 products each at £10. Order them in a repeated pattern so that
> > > each supplier has a product listed in the top 6 for £10.
>
> > > supplier 1 - product title - $10
> > > supplier 2 - product title - $10
> > > supplier 3 - product title - $10
> > > supplier 4 - product title - $10
> > > supplier 5 - product title - $10
> > > supplier 6 - product title - $10
> > > supplier 1 - product title - $10
> > > supplier 2 - product title - $10
> > > supplier 3 - product title - $10
> > > and so on....
>
> > > Getting who comes where in the pattern list is already covered but
> > > actually getting the results to follow this pattern inside the price
> > > blocks is the problem.
>
> > > We found that some suppliers have large blocks of products in the
> > > database and hence for price blocks they are appearing in some
> > > instances all on the first page. Which isnt fair really to other
> > > suppliers.
>
> > > The products table i can't touch, but i can add tables and change the
> > > supllier records no problem.
>
> > > Thanks for your help,
>
> > > Tom
>
> > It's not a perfect answer but by far the simplest way is ORDER BY
> > RAND(),PRICE
>
> I meant ORDER BY PRICE, RAND()

Tryed that strawberry but it doesnt do exactly what im after and the
performance hit makes it not an option. We're talking over 500,000
records

Does anyone have any ideas?