Page 1 of 1

order by date desc limit to 1 and have it grouped by

Posted: Mon Apr 01, 2013 1:12 am
by lovelf
This is the mysql query:

Code: Select all

$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 get one row in which there is a unique id field, or id2 field in which either one of the two is the logged in user $uid. Ordered by datetimep

Table structure with rows in it:

Image

What I am after is the last conversation in between the logged in user and any other user. Last row which holds in the longtext the conversation and it's timestamp and whether the sender was the logged in user or the other party, etc.

Re: order by date desc limit to 1 and have it grouped by

Posted: Wed Apr 10, 2013 10:07 am
by Jade
This find the last message set to the logged in user (assuming id2 is the recipient and id is the sender, you should name your table fields better) that hasn't been viewed yet. One thing I'm not sure about -- why are you grouping the messages if you just want the last one? There's no need to group them if you only want the last message but I included the grouping below anyways just in case there's some other reason you need it.

Code: Select all

SELECT * FROM commentsvv WHERE id2='$id_logged_in_user' AND status_id2 = 0 ORDER BY sbid DESC GROUP BY id LIMIT 1