#1
  1. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Nobbies beach, Gold Coast. It's beautiful.
    Posts
    2,574
    Rep Power
    171

    ORDER BY question


    Hello;

    I want to order by 2 columns:
    1 - atless_deals.id
    2 - atless_deal_photo.main

    For example

    Deal 5 then deal 4 then deal 3...But for deal 5, show the photo that has biggest value for its column "main". My query shows the latest deals but ignores the main column in photo table! What am I doing wrong?

    Thank you


    Code:
    SELECT *,
           atless_deals.id AS DID
    FROM   atless_deals
           LEFT OUTER JOIN atless_deal_photo
                        ON atless_deal_photo.deal_id = atless_deals.id
    WHERE  status = 1
    GROUP  BY atless_deals.id
    ORDER  BY atless_deals.id,
              atless_deal_photo.main DESC
    Tables
    Code:
    --
    -- Table structure for table `atless_deals`
    --
    
    CREATE TABLE `atless_deals` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `title` varchar(255) NOT NULL,
      `business_id` int(11) NOT NULL,
      `status` varchar(20) NOT NULL,
      `admin_status` varchar(20) NOT NULL,
      `date_added` date NOT NULL,
      `description` text NOT NULL,
      `start_date` date NOT NULL,
      `end_date` date NOT NULL,
      `original_value` decimal(6,2) NOT NULL,
      `deal_value` decimal(6,2) NOT NULL,
      `offers` int(11) NOT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=87 ;
    
    -- --------------------------------------------------------
    
    --
    -- Table structure for table `atless_deal_photo`
    --
    
    CREATE TABLE `atless_deal_photo` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `deal_id` int(11) NOT NULL,
      `file` varchar(255) NOT NULL,
      `main` int(11) NOT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=88 ;
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,026
    Rep Power
    4210
    Originally Posted by zxcvbnm
    What am I doing wrong?
    using GROUP BY
    r937.com | rudy.ca
    please visit Simply SQL and buy my book
  4. #3
  5. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Nobbies beach, Gold Coast. It's beautiful.
    Posts
    2,574
    Rep Power
    171
    Originally Posted by r937
    using GROUP BY
    Ok, how can I now show each ad only once withour GROUP BY?
    SELECT atless_deals.id AS DID,
    atless_deal_photo.id AS PH_ID
    FROM atless_deals
    LEFT OUTER JOIN atless_deal_photo
    ON atless_deal_photo.deal_id = atless_deals.id
    WHERE status = 'active'
    ORDER BY atless_deals.id,
    atless_deal_photo.main DESC
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,026
    Rep Power
    4210
    Originally Posted by zxcvbnm
    Ok, how can I now show each ad only once withour GROUP BY?
    by deciding which photo you want to show, and putting in a filter condition for it

    also, if each ad is going to show up only once, then there's not much point in sorting on any photo column after you sort by the ad id
    r937.com | rudy.ca
    please visit Simply SQL and buy my book
  8. #5
  9. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Nobbies beach, Gold Coast. It's beautiful.
    Posts
    2,574
    Rep Power
    171
    Originally Posted by r937
    by deciding which photo you want to show, and putting in a filter condition for it
    I want to show the photo that has value 1 for column "main".

    Each ad can only have 1 photo with value 1 for the column "main".
    Thanks
  10. #6
  11. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,026
    Rep Power
    4210
    Code:
    SELECT atless_deals.*
         , atless_deal_photo.file
      FROM atless_deals
    LEFT OUTER 
      JOIN atless_deal_photo
        ON atless_deal_photo.deal_id = atless_deals.id
       AND atless_deal_photo.main = 1
     WHERE atless_deals.status = 1
    ORDER 
        BY atless_deals.id DESC
    see? no GROUP BY
    r937.com | rudy.ca
    please visit Simply SQL and buy my book
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Mar 2008
    Posts
    1,927
    Rep Power
    378
    I think the OP wants the minimum `main` for each result. The standard solution zxcvbnm (and I feel like we've covered this before) does use a GROUP BY (in a subquery) and is one of a variety of so called 'groupwise max' or 'top-n' queries.
  14. #8
  15. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,026
    Rep Power
    4210
    Originally Posted by cafelatte
    I think the OP wants the minimum `main` for each result.
    i prefer not to second guess, especially when the requirements are as clear as in post #5

    r937.com | rudy.ca
    please visit Simply SQL and buy my book
  16. #9
  17. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Nobbies beach, Gold Coast. It's beautiful.
    Posts
    2,574
    Rep Power
    171
    Oh no!I didn't consider one scenario! What happens when the user has not selected main photo for the ad? So all photos
    have set the "main" column to '0'. I have the solution that Rudy is not a fan of. Back to square 1. Ichh

    SELECT atless_deals.id AS DID, photo_query.file AS file
    FROM atless_deals
    LEFT OUTER
    JOIN
    (SELECT file, deal_id FROM atless_deal_photo ORDER BY main DESC) AS photo_query
    ON photo_query.deal_id = atless_deals.id
    WHERE atless_deals.status =:status
    GROUP BY atless_deals.id
    ORDER
    BY atless_deals.id DESC
    Last edited by zxcvbnm; October 14th, 2012 at 07:16 PM.
  18. #10
  19. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,026
    Rep Power
    4210
    Originally Posted by zxcvbnm
    What happens when the user has not selected main photo for the ad?
    please, try the query in post #6

    it covers that scenario
    r937.com | rudy.ca
    please visit Simply SQL and buy my book

IMN logo majestic logo threadwatch logo seochat tools logo