Order By - Fair ordering on eccomerce site - MySQL

[Tom]

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 ?

[Peter]

On 21 Feb 2007 04:28:57 -0800, Tom Wilson 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.

--
4. Shooting is not too good for my enemies.
--Peter Anspach's list of things to do as an Evil Overlord

[Captain]

On 21 Feb, 14:11, "Peter H. Coffin" <com> wrote: 

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

[Peter]

On 21 Feb 2007 06:53:19 -0800, Captain Paralytic wrote: 
>
> Good answer apart from missing the first fact:
> - I cant change products database in any way[/ref]

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

[Captain]

On 21 Feb, 15:19, "Peter H. Coffin" <com> wrote: [/ref]
> 
>
> 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[/ref]

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.

[Tom]

On Feb 21, 4:44 pm, "Captain Paralytic" <com>
wrote: 
> [/ref]
> [/ref]
> 
> 
>
> 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.[/ref]

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

[strawberry]

On Feb 21, 5:05 pm, "Tom Wilson" <com> wrote: 
> [/ref]
> [/ref]
> [/ref]
> [/ref]
> [/ref]
> 
>
> 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[/ref]

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

[strawberry]

On Feb 21, 6:16 pm, "strawberry" <com> wrote: 
> [/ref]
> [/ref]
> [/ref]
> [/ref]
> [/ref]
> [/ref]
> [/ref]
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
>
> It's not a perfect answer but by far the simplest way is ORDER BY
> RAND(),PRICE[/ref]

I meant ORDER BY PRICE, RAND()

[Tom]

On Feb 21, 6:19 pm, "strawberry" <com> wrote: 
> [/ref]
> [/ref]
> [/ref]
> [/ref]
> [/ref]
> [/ref]
> [/ref]
> [/ref]
> [/ref]
> [/ref]
> [/ref]
> [/ref]
> [/ref]
> [/ref]
> [/ref]
> [/ref]
> [/ref]
> [/ref]
> 
>
> I meant ORDER BY PRICE, RAND()[/ref]

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?