Ascending order of yearly archives

Hey there,

I stumbled upon this question but I can’t seem to contact the author, because I’m still too new here.

I’m using this snippet on my archives.php page to display my posts sorted by years. It works great, now I want to know how to sort the posts in descending order, so that the newest posts are displayed on top.

// get years that have posts
$years = $wpdb->get_results( "SELECT YEAR(post_date) AS year FROM wp_posts WHERE post_type = 'post' AND post_status = 'publish' GROUP BY year DESC" );

foreach ( $years as $year ) {
// get posts for each year
    $posts_this_year = $wpdb->get_results( "SELECT post_title FROM wp_posts WHERE post_type = 'post' AND post_status = 'publish' AND YEAR(post_date) = '" . $year->year . "'" );

    echo '<h2>' . $year->year . '</h2><ul>';
    foreach ( $posts_this_year as $post ) {
        echo '<li>' . $post->post_title . '</li>';
    }
    echo '</ul>';
}

This is what it looks like at the moment:

image

Every help is very much appreciated. Thank you from Berlin.

Solutions Collecting From Web of "Ascending order of yearly archives"

You will have to define post order in second SQL query too. Try this one.

// get years that have posts
$years = $wpdb->get_results( "SELECT YEAR(post_date) AS year FROM wp_posts WHERE post_type = 'post' AND post_status = 'publish' GROUP BY year DESC" );

foreach ( $years as $year ) {

  // get posts for each year
  $posts_this_year = $wpdb->get_results( "SELECT * FROM wp_posts WHERE post_type = 'post' AND post_status = 'publish' AND YEAR(post_date) = '" . $year->year . "' ORDER BY post_date DESC" );

  echo '<h2>' . $year->year . '</h2><ul>';
  foreach ( $posts_this_year as $post ) {
    echo '<li>' . $post->post_title . '</li>';
  }
  echo '</ul>';

}