Pagination in author.php

<?php
                                    // lets get all the other ad listings by this author
                                    $sql = $wpdb->prepare("SELECT * FROM $wpdb->posts "
                                         . "WHERE post_author = $curauth->ID "
                                         . "AND post_type = 'post' "
                                         . "AND post_status = 'publish' "
                                         . "ORDER BY ID DESC");


                                    $authorposts = $wpdb->get_results($sql);
                                    ?>


                                    <?php if ($authorposts): ?>


                                            <?php foreach ($authorposts as $post): ?>
                                            <li>
                                                <a href="<?php the_permalink() ?>"><?php the_title(); ?></a>
                                            </li>


                                            <?php endforeach; ?>


                                    <?php else: ?>


                                            <p><?php _e('No ads by this poster yet.','cp'); ?></p>


                                    <?php endif; ?>