Set ordering for product results

Hi Guys,

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 12 Mar, 14:58, "Tom Wilson" <t...@actual-systems.com> wrote:
> Hi Guys,
>
> 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

Well, I think this was fairly well covered by others last time but, in
case it wasn't mentioned before, a brief coda:

Results like this:
item_id seller_id
1 1
2 1
3 2
4 2
5 3
6 3

can be ordered by 'ranking' as follows:

item_id seller_id rank
1 1 1
3 2 1
5 3 1
2 1 2
4 2 2
6 3 2

using a query like this:

SELECT a.item_id, a.seller_id, count( * ) rank
FROM alist A
JOIN alist B ON ( A.seller_id = B.seller_id
AND A.item_id >= B.item_id )
GROUP BY A.seller_id, A.item_id
ORDER BY rank, seller_id

On Mar 12, 6:52 pm, "strawberry" <zac.ca...@gmail.com> wrote:
> On 12 Mar, 14:58, "Tom Wilson" <t...@actual-systems.com> wrote:
>
>
>
> > Hi Guys,
>
> > 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
>
> Well, I think this was fairly well covered by others last time but, in
> case it wasn't mentioned before, a brief coda:
>
> Results like this:
> item_id seller_id
> 1 1
> 2 1
> 3 2
> 4 2
> 5 3
> 6 3
>
> can be ordered by 'ranking' as follows:
>
> item_id seller_id rank
> 1 1 1
> 3 2 1
> 5 3 1
> 2 1 2
> 4 2 2
> 6 3 2
>
> using a query like this:
>
> SELECT a.item_id, a.seller_id, count( * ) rank
> FROM alist A
> JOIN alist B ON ( A.seller_id = B.seller_id
> AND A.item_id >= B.item_id )
> GROUP BY A.seller_id, A.item_id
> ORDER BY rank, seller_id

Hi Strawberry,

Thanks for your response, this is in the general area but ive had a
few problems implmenting it correctly.
no records in the database are uniquely identifable they are using 2
separate columns becuase of this ive added a auto-increment column so
i can at least get this ranking query to work.

Right now what i have is the products returning in thier usual order
(by price etc) but when sorting by rank its using a rank before the
where clause is invoke.

So what i get is ranked item before i implement the where clause to
pull out certain records. what i want is to rank the pulled records
after the where clause.

Any ideas?

On Mar 14, 12:47 pm, "Tom Wilson" <t...@actual-systems.com> wrote:
> On Mar 12, 6:52 pm, "strawberry" <zac.ca...@gmail.com> wrote:
>
>
>
> > On 12 Mar, 14:58, "Tom Wilson" <t...@actual-systems.com> wrote:
>
> > > Hi Guys,
>
> > > 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
>
> > Well, I think this was fairly well covered by others last time but, in
> > case it wasn't mentioned before, a brief coda:
>
> > Results like this:
> > item_id seller_id
> > 1 1
> > 2 1
> > 3 2
> > 4 2
> > 5 3
> > 6 3
>
> > can be ordered by 'ranking' as follows:
>
> > item_id seller_id rank
> > 1 1 1
> > 3 2 1
> > 5 3 1
> > 2 1 2
> > 4 2 2
> > 6 3 2
>
> > using a query like this:
>
> > SELECT a.item_id, a.seller_id, count( * ) rank
> > FROM alist A
> > JOIN alist B ON ( A.seller_id = B.seller_id
> > AND A.item_id >= B.item_id )
> > GROUP BY A.seller_id, A.item_id
> > ORDER BY rank, seller_id
>
> Hi Strawberry,
>
> Thanks for your response, this is in the general area but ive had a
> few problems implmenting it correctly.
> no records in the database are uniquely identifable they are using 2
> separate columns becuase of this ive added a auto-increment column so
> i can at least get this ranking query to work.
>
> Right now what i have is the products returning in thier usual order
> (by price etc) but when sorting by rank its using a rank before the
> where clause is invoke.
>
> So what i get is ranked item before i implement the where clause to
> pull out certain records. what i want is to rank the pulled records
> after the where clause.
>
> Any ideas?

Not sure without looking at the structure - but, depending on your
version, it sounds like a subquery could work:

SELECT x.*,count(*) rank FROM
(SELECT a,b,c FROM mytable WHERE condition)x
GROUP BY a
ORDER BY rank

Why not post part of your schema? Incidentally, if the unique
identifier really IS comprised of two (or more) fields, you can of
course always CONCATENATE them:

SELECT
CONCATENATE(part_of_the_identifier,the_other_part_ of_the_identifier)
AS item_id, etc