#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2013
    Posts
    2
    Rep Power
    0

    Order by date limit to one and group by sub id


    PHP Code:
    $r=mysql_query(" ( SELECT * FROM commentsvv AS dt WHERE FIND_IN_SET(id,'$dset')>0 AND id2='$uid' AND status_id2='0' ORDER BY datetimep DESC LIMIT 1) UNION ( SELECT * FROM commentsvv AS dt WHERE FIND_IN_SET(id2,'$dsetv')>0 AND id='$uid' AND status_id='0' ORDER BY datetimep DESC LIMIT 1) "); 
    I want to have one record for each id and id2 (many in total) ordered by timestamp

    It's retrieving one result overall for id and for id2. I would want to retrieve one result for each member in $dset or $dsetv if it were available, meaning group by id in the first query, in the second group by id2 and order by datetimep desc limit 1. Can't get it going.

    Image url with table structure and rows in:
    imageshack.us/photo/my-images/832/snap24.png

    datetimep, id and id2 are the focus, I need to get one result for each id and id2, the last one (order by datetimep) but can't do both, group and order in same query, also either id or id2 must equal to the logged in user $uid
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,204
    Rep Power
    4279
    Originally Posted by lovelf
    I want to have one record for each id and id2 (many in total) ordered by timestamp
    okay, let's clarify

    one for id, and another one for id2? or one for each combination of id and id2?

    Originally Posted by lovelf
    I would want to retrieve one result for each member in $dset or $dsetv if it were available
    i don't understand this at all, since i don't know what those php variables are

    Originally Posted by lovelf
    Image url with table structure and rows in:
    imageshack.us/photo/my-images/832/snap24.png
    even when i use the zoom button, this is too small to read
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo