ORDER BY question

Dev Shed Forums Sponsor:

October 13th, 2012, 07:18 PM

zxcvbnm

A Change of Season

Join Date: Mar 2004

Posts: 1,587

Time spent in forums: 2 Weeks 4 Days 19 h 8 m 33 sec
Reputation Power: 70

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 ;

October 13th, 2012, 11:43 PM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 26,355

Time spent in forums: 3 Months 1 Week 2 Days 4 h 25 m
Reputation Power: 4140

Quote:

Originally Posted by zxcvbnm

What am I doing wrong?

using GROUP BY

__________________
r937.com | rudy.ca
please visit Simply SQL and buy my book

October 13th, 2012, 11:55 PM

zxcvbnm

A Change of Season

Join Date: Mar 2004

Posts: 1,587

Time spent in forums: 2 Weeks 4 Days 19 h 8 m 33 sec
Reputation Power: 70

Quote:

Originally Posted by r937

using GROUP BY

Ok, how can I now show each ad only once withour GROUP BY?

Quote:

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

October 14th, 2012, 01:02 AM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 26,355

Time spent in forums: 3 Months 1 Week 2 Days 4 h 25 m
Reputation Power: 4140

Quote:

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

October 14th, 2012, 01:16 AM

zxcvbnm

A Change of Season

Join Date: Mar 2004

Posts: 1,587

Time spent in forums: 2 Weeks 4 Days 19 h 8 m 33 sec
Reputation Power: 70

Quote:

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

October 14th, 2012, 02:00 AM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 26,355

Time spent in forums: 3 Months 1 Week 2 Days 4 h 25 m
Reputation Power: 4140

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

October 14th, 2012, 03:08 AM

cafelatte

Contributing User

Dev Shed Intermediate (1500 - 1999 posts)

Join Date: Mar 2008

Posts: 1,923

Time spent in forums: 1 Month 5 Days 16 h 21 m 8 sec
Reputation Power: 377

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.

October 14th, 2012, 03:14 AM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 26,355

Time spent in forums: 3 Months 1 Week 2 Days 4 h 25 m
Reputation Power: 4140

Quote:

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

October 14th, 2012, 06:57 PM

zxcvbnm

A Change of Season

Join Date: Mar 2004

Posts: 1,587

Time spent in forums: 2 Weeks 4 Days 19 h 8 m 33 sec
Reputation Power: 70

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

Quote:

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.