order by then group by

Question

how can i first order by and then group by

select saf.*
from sm_artists_followers saf,
     sm_artists_meta sam 
where sam.fk_sm_artist_id = 13
  and saf.artist_ID = sam.artist_ID 
  and DATE(saf.scrap_date) = '2023-10-22' 
group by website_ID  
order by saf.scrap_date desc;

sm_artists_followers

Answer 1

In SQL you cannot directly perform a GROUP BY operation before an ORDER BY operation. If you want to first order by a certain column and then perform a GROUP BY, you'll typically need to use a subquery

SELECT sub.* 
FROM (
    SELECT saf.* 
    FROM sm_artists_followers saf 
    JOIN sm_artists_meta sam ON saf.artist_ID = sam.artist_ID
    WHERE sam.fk_sm_artist_id = 13 
      AND DATE(saf.scrap_date) = '2023-10-22'
    ORDER BY saf.scrap_date DESC
) sub
GROUP BY sub.website_ID;