Simple SQL question

Question

(I couldn't make up a good title, sorry)

So I use this pagination:

$page = $_GET['page'];

$max = 5;

// if $page is empty, set page number to 1
if (empty($page)) $page = 1;

$limit = ($page - 1) * $max; 

$sql = mysql_query("SELECT * FROM posts ORDER BY date DESC LIMIT $limit, $max");

$totalres = mysql_result(mysql_query("SELECT COUNT(id) AS tot FROM posts WHERE tid =     1"),0);    

$totalpages = ceil($totalres / $max);
?>

This works, but what I want is that the post with the oldest date always stays on top even if I change page. How can I do this? I know you can but I cant remember how.

Hopefully you do, thanks

Answer 1

You probably need to do a union query since what you want is really the combination of two pieces of data...

1. The oldest post
2. All of the other posts minus the oldest post adjusted for page position...

    select * from posts order by date DESC limit 0,1
    union
    select * from posts order by date desc limit $start,$end

Now adjust your values so start is either the page you want minimum 1 (if it's 0, your oldest post will show twice)