#1
  1. No Profile Picture
    competitions at lottos.com.au
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2003
    Posts
    252
    Rep Power
    12

    Select all rows and select just first row?


    Currently do a select to produce rows of posts for a specific topic:

    $queryposts = $db->query("SELECT p.pid, p.author, p.message, p.subject, p.dateline, m.username FROM posts p INNER JOIN members m ON m.username = p.author WHERE.... ORDER BY p.pid ASC");



    I would like to ALSO select the FIRST post row for the same topic, assign it to a variable. I could do this via:


    $firstpostquery = $db->query("SELECT pid FROM posts WHERE... ORDER BY dateline LIMIT 1");
    $isthefirstpost = $db->fetch_array($firstpostquery);
    $db->free_result($firstpostquery);


    However the second select seems inefficient because I'm already grabbing all rows in the first select and the second select has to go through the entire posts table again.


    Is there a way to use the first select and also do a sub select for also obtaining the first row - or some other method that is more efficient?
    Best website for competitions online to win anything and everything.
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,172
    Rep Power
    4274
    since you're retrieving all the rows that meet your criteria, the function to extract the first row's data should be performed in your application language

    that's not only the easiest but also the most efficient
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    competitions at lottos.com.au
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2003
    Posts
    252
    Rep Power
    12
    Originally Posted by r937
    since you're retrieving all the rows that meet your criteria, the function to extract the first row's data should be performed in your application language

    that's not only the easiest but also the most efficient

    Thanks Rudy. Great advice. In case anyone else is looking for a similar solution, a good example can be found here:
    http://stackoverflow.com/questions/4742659/php-loop-through-query-first-row
    Best website for competitions online to win anything and everything.

IMN logo majestic logo threadwatch logo seochat tools logo