ORDER BY not working with GROUP BY

Databases
1

Hi!

I am trying to order my SQL query by date with the most recent date entry first. However, when I use GROUP BY, the ORDER BY statement is ignored and it just shows the first entry it finds.

Here’s the SQL statement I am using:


select dt_offers.*, dt_users.first_name, dt_users.last_name, dt_users.email_address, dt_domains.domain as domain_name, dt_domains.currency from dt_offers left join dt_domains on dt_offers.domain_id = dt_domains.id left join dt_users on dt_users.id = dt_offers.bidder_id where dt_offers.owner_id='1' group by dt_offers.domain_id order by dt_offers.date desc

Here’s the data in the “offers” table:


INSERT INTO `dt_offers` (`id`, `domain_id`, `owner_id`, `bidder_id`, `amount`, `status`, `payment_method`, `buyer_registrar`, `buyer_registrar_account`, `date`, `seller_counter`, `seller_notes`, `seller_date`, `buyer_counter`, `buyer_notes`, `buyer_date`, `transaction_log`) VALUES
(1, 537, 1, 2, '200.00', '10', NULL, '', '', '2010-07-06 05:15:18', NULL, NULL, NULL, NULL, NULL, NULL, 'Offer submitted|Domain sent to auction|'),
(2, 537, 1, 11, '250.00', '10', NULL, NULL, NULL, '2010-07-06 06:01:22', NULL, NULL, NULL, NULL, NULL, NULL, 'Offer submitted|');

The SQL statement above should select row 2 from the database, but instead it selects row 1.

Any ideas why this is?

2

Try this


select 
    dt_offers.*
  , dt_users.first_name
  , dt_users.last_name
  , dt_users.email_address
  , dt_domains.domain as domain_name
  , dt_domains.currency 
from dt_offers 
inner join 
  (select
       domain_id
     , MAX(`date`) as lastdate
   from dt_offers
   group by domain_id
  ) AS o
on dt_offers.domain_id = o.domain_id
and dt_offers.`date` = o.lastdate
left join dt_domains 
on dt_offers.domain_id = dt_domains.id 
left join dt_users 
on dt_users.id = dt_offers.bidder_id 
where dt_offers.owner_id='1'
3

Thank you, that seemed to work perfectly!!

4

Yes I know it should only return 1 row, but it is returning the wrong row. I want it to return the second row (id: 2) but it returns the first row (id: 1).

Do you see my issue?

5

Because it should select only 1 :slight_smile:
You group by domain_id, both rows have the same domain_id, so it will return only 1 row.