order by then group by

October 26, 2023

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

>Solution :

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;