Results 1 to 7 of 7

Thread: ordering (group by)

  1. #1
    Join Date
    Nov 2007
    Posts
    151
    Thanks
    67
    Thanked 0 Times in 0 Posts

    Default ordering (group by)

    I have a table named, "myorder".
    I want to select the information from it as described on the image below:
    Increasing id, and grouping the same product_ids together.

    Click image for larger version. 

Name:	11.jpg 
Views:	360 
Size:	22.2 KB 
ID:	5344

    My sql command didn't work out:
    PHP Code:
    SELECT f.*
    FROM myorder f
    INNER JOIN 
    (SELECT product_idMAX(id) AS Ordered GROUP BY product_ido
    ON f
    .product_id o.product_id
    ORDER BY o
    .Ordered 

  2. #2
    Join Date
    Apr 2008
    Location
    So.Cal
    Posts
    3,643
    Thanks
    63
    Thanked 517 Times in 503 Posts
    Blog Entries
    5

    Default

    GROUP BY is an aggregate modifier - it produces a single row and value for all of the rows it groups (typically, an average).

    What you're describing is simply sorting twice.
    Code:
    SELECT ...
        ORDER BY f.product_id, o.Ordered;
    If you need more help, please share your table schemas.

  3. The Following User Says Thank You to traq For This Useful Post:

    d-machine (02-07-2014)

  4. #3
    Join Date
    Nov 2007
    Posts
    151
    Thanks
    67
    Thanked 0 Times in 0 Posts

    Default

    Can you please write the full code? (assuming my table is exactly as described on the image).

  5. #4
    Join Date
    Apr 2008
    Location
    So.Cal
    Posts
    3,643
    Thanks
    63
    Thanked 517 Times in 503 Posts
    Blog Entries
    5

    Default

    Your table is not described "exactly" in the image you posted. Please show your table schema (both tables, unless you are joining to itself) and describe which columns you actually want. (You can use SHOW CREATE TABLE `your_table_name_here` to get the schema information.)

  6. #5
    Join Date
    Nov 2007
    Posts
    151
    Thanks
    67
    Thanked 0 Times in 0 Posts

    Default

    Here is the table myorder from the database:
    Click image for larger version. 

Name:	2.jpg 
Views:	154 
Size:	22.1 KB 
ID:	5367
    What I want is to print the table using the following method (on this image I havn't mentioned the other columns, but they are also necessary:
    Click image for larger version. 

Name:	11.jpg 
Views:	156 
Size:	22.2 KB 
ID:	5368

  7. #6
    Join Date
    Apr 2008
    Location
    So.Cal
    Posts
    3,643
    Thanks
    63
    Thanked 517 Times in 503 Posts
    Blog Entries
    5

    Default

    Okay - I misunderstood how you wanted to sort these. What I suggested (ORDER BY col1, col2) only uses col2 as a tie breaker. I would recommend using a stored procedure to do this, or simply performing the second sort in your application (it would be trivial to do so while retrieving the results, for example). I'm not sure there is a single-query way to get the rows in the order you want, but I'll do a bit more research.

  8. #7
    Join Date
    Nov 2007
    Posts
    151
    Thanks
    67
    Thanked 0 Times in 0 Posts

    Default

    I really appreciate if you could help me to write a procedure or queries for that! thanks in advance

Similar Threads

  1. Ordering results in a union
    By fileserverdirect in forum MySQL and other databases
    Replies: 3
    Last Post: 04-22-2011, 03:40 AM
  2. xml ordering with php
    By insanity in forum PHP
    Replies: 0
    Last Post: 11-06-2010, 07:41 PM
  3. ordering script (shopping)
    By viktor in forum Looking for such a script or service
    Replies: 7
    Last Post: 01-23-2008, 02:21 AM
  4. Ordering Result Alphabetically
    By Titan85 in forum MySQL and other databases
    Replies: 3
    Last Post: 04-15-2007, 10:00 PM
  5. MySQL-- Ordering results by...
    By djr33 in forum MySQL and other databases
    Replies: 8
    Last Post: 09-18-2006, 06:33 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
  •