Question 293466 in Mysql Question posted in
The official MySQL documentation can be found here.

MySQL: Group by one column but order by another

I have a table with restaurant names (venues)

id venue
1 Venue 1
2 Venue 2
3 Venue 3

And a table with special offers (specials)

venue_id offer modified
1 10% off 2023-11-17
2 20% off 2023-11-16
3 30% off 2023-11-10
3 20% off 2023-11-14
3 50% off 2023-11-19

I am attempting to return the offers in reverse chronological order so the most recent appear first, but also group the venue names together

Any sort of join between these two tables ordered by Modified will return

SELECT v.venue, s.special, s.modified
FROM venues v, specials s
WHERE s.venue_id = v.id
ORDER BY modified DESC
venue offer modified
Venue 3 50% off 2023-11-19
Venue 1 10% off 2023-11-17
Venue 2 20% off 2023-11-16
Venue 3 20% off 2023-11-14
Venue 3 30% off 2023-11-10

The problem is that I wish to also group the venues together so they can be output together.

Any attempt to GROUP BY venue will omit 2 of the records for Venue 3.

I want to order by Modified but group them together (which in my head defies logic) but ideally by grouping them based on the single most recent record for a venue (if that makes sense):

Venue Special Modified
Venue 3 50% off 2023-11-19
Venue 3 20% off 2023-11-14
Venue 3 30% off 2023-11-10
Venue 1 10% off 2023-11-17
Venue 2 20% off 2023-11-16

Have attempted different types of subquery and joins but it’s way to advanced for me.

Tags:

4

Answers


  1. 0

    If you want to group by multiple value then you can just do grouping query, for example :

    SELECT v.venue, s.special, s.modified
FROM venues v INNER JOIN specials s ON s.venue_id = v.id
WHERE {additional filter}
GROUP BY v.venue, s.modified

    can you just elaborate more on your question?

    I want to order by Modified but group them together (which in my head
    defies logic) but ideally by grouping them based on the single most
    recent record for a venue (if that makes sense):

    It will actually better if based on dataset you provide you can give us your expected output because at this point your purpose is unclear

    Login or Signup to reply.
  2. 0

    You don’t need group by, do you?

    SELECT v.venue, s.special, s.modified
FROM venues v, specials s
WHERE s.venue_id = v.id
ORDER BY venue, modified DESC

    (or Venue desc, modified desc if you also want them in reverse)

    Login or Signup to reply.
  3. 0

    using a sub query to append the max modified to every venue row makes ordering simpler

    DROP TABLE IF EXISTS T;
create table t
(venue varchar(10), offer varchar(10),  modified date);
insert into t values
('Venue 3', '50% off',  '2023-11-19'),
('Venue 1', '10% off',  '2023-11-17'),
('Venue 2', '20% off',  '2023-11-16'),
('Venue 2', '20% off',  '2023-11-19'),
('Venue 3', '20% off',  '2023-11-14'),
('Venue 3', '30% off',  '2023-11-10');


select *,(select max(modified) from t t1 where t1.venue = t.venue) maxmd
from t
order by maxmd desc ,venue,modified desc;

    https://dbfiddle.uk/GsHy7TTX

    note in the event of ties ,as in my sample data, you may need some other criterion for ordering.

    Login or Signup to reply.
  4. 0

    I think this is probably what you want:

    SELECT v.venue, s.offer, s.modified
FROM specials s
JOIN venues v ON s.venue_id = v.id
ORDER BY
  (SELECT MAX(modified) FROM specials WHERE venue_id = v.id) DESC,
  venue_id,
  modified DESC;

    Outputs:

    venue offer modified
    Venue 3 50% off 2023-11-19
    Venue 3 20% off 2023-11-14
    Venue 3 30% off 2023-11-10
    Venue 1 10% off 2023-11-17
    Venue 2 20% off 2023-11-16

    Here’s a db<>fiddle.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search