Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2002
    Posts
    24

    Question Unanswered: Sorting results, starting at 2 or 3, then loop until finished. =\

    Hi folks,

    I have a strange situation here and I simply can't come up with a solution. What started as a normal company listing, sorted alphabetically, has taken a strange turn with my client. He would like the results to 'rotate' arbitrarily (every 5 days) so that the listing that was in the first position of the results will be shifted to last, then the second company will be first, the 3rd second, and so on. They were originally sorted alphabetically, but now they'll be alphabetically but starting with "B" for example, going through "Z", then showing the "A" records.

    Here is how the data is currently retrieved:

    SELECT company_id, company_name, company_slogan, company_brief, company_stars, company_discount, company_phone, company_email
    FROM companies
    WHERE company_category = $_GET['company_category']
    ORDER BY company_name ASC

    Is there a way to adjust that statement to have an alphabetical result, but starting with record #2 -> end, then showing #1?

    It would have to be dynamic, adjusting the starting record according to a 5-day interval, but I'm sure I can figure that out. I just don't even know where to start with this one.

    Thanks to anyone who can help!
    -DW

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT company_id
         , company_name
         , CASE WHEN company_name >= 'B'  
                THEN 9  -- B thru Z sort first
                ELSE 37 -- the rest sort second
             END AS sortkey
         , company_slogan
         , company_brief
         , company_stars
         , company_discount
         , company_phone
         , company_email
      FROM companies
     WHERE company_category = $_GET['company_category']
    ORDER 
        BY sortkey
         , company_name ASC
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Oct 2002
    Posts
    24

    Wow

    Hi Rudy, thanks for your response so quickly. Could you help me understand why 9 and 37 are the numbers you used here? I was planning on modifying it to fit the situation, i.e. the 5-day interval, or have only one company go to the back of the line and keep the other A's etc.

    Your technique works well though, I just gave it a run and it does exactly what I hoped, thanks for that!

    -DW

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    9 & 37, especially when joined together are a magic number as far as Rudy is concerned.

    you could use any numbers providing the one you want first is has the lowest value
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by DataWho
    ... or have only one company go to the back of the line and keep the other A's etc.
    in the first line of the CASE expression, instead of 'B', just code the name of the company you want at the top of the list
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Oct 2002
    Posts
    24

    Nice

    OK this works great as long as there is a definable company name that I add to the SELECT, but what about a more all-encompassing query that could sort any list? For example, in some pages there will be A's, B's, C's etc., but some pages will only have two or three companies starting with other letters.

    The idea I suppose was to have a sort of conveyor system that rotates the companies alphabetically every so often.

    Thanks for this by the way, you're being incredibly helpful.
    -DW

  7. #7
    Join Date
    Oct 2002
    Posts
    24

    Solved!

    Hi folks,

    I thought I'd come back with a solution I came up with that may help somebody some day

    The solution here was to use UNION with two LIMITed SELECT statements. The first one selects the whole set minus the "offset" number which is related in my case to the day of the year, and the second one selects the first three that got left out.

    (SELECT company_id, company_name, company_slogan, company_brief, company_stars, company_discount, company_phone, company_email
    FROM companies
    WHERE company_category = $_GET['company_category']
    ORDER BY company_name ASC
    LIMIT $conveyor_offset , $maxRows_category_list)
    UNION
    (SELECT company_id, company_name, company_slogan, company_brief, company_stars, company_discount, company_phone, company_email
    FROM companies
    WHERE company_category = $_GET['company_category']
    ORDER BY company_name ASC
    LIMIT 0,$conveyor_offset )

    The offset was a couple of lines of PHP like this:
    $dayofyear = date("z");
    $conveyor_offset = (round($dayofyear / 5) % $maxRows_category_list);

    ...so for what I figure it will loop through to the next record after 5 days, and then repeat the procedure until all records have looped.

    Thanks for the help! It got me started on the path for sure.

Posting Permissions

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