Professional Web Applications Themes

Set ordering for product results - MySQL

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 - ...

  1. #1

    Default 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

    Tom Guest

  2. #2

    Default Re: Set ordering for product results

    On 12 Mar, 14:58, "Tom Wilson" <com> wrote: 

    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

    strawberry Guest

  3. #3

    Default Re: Set ordering for product results

    On Mar 12, 6:52 pm, "strawberry" <com> wrote: 







    >
    > 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[/ref]

    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?

    Tom Guest

  4. #4

    Default Re: Set ordering for product results

    On Mar 14, 12:47 pm, "Tom Wilson" <com> wrote: 
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]






    >
    > 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?[/ref]

    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

    strawberry Guest

Similar Threads

  1. Grouping and ordering
    By miken32 in forum MySQL
    Replies: 7
    Last Post: July 12th, 12:03 AM
  2. Ordering by date ???
    By TomT in forum ASP Database
    Replies: 21
    Last Post: August 17th, 08:11 PM
  3. Ordering by a variable
    By Amos in forum Microsoft SQL / MS SQL Server
    Replies: 5
    Last Post: August 5th, 05:23 PM
  4. Grouping and Ordering
    By Damian in forum Microsoft SQL / MS SQL Server
    Replies: 1
    Last Post: July 16th, 07:08 AM
  5. Product activation when you've lost your Product ID sticker
    By larry samuels MS-MVP \(XP Shell/User\) in forum Windows XP/2000/ME
    Replies: 2
    Last Post: July 5th, 10:38 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139