group by and order by in mysql query

Question

I am working on dating website with php and mysql as backend. I've two tables. First table is user table where all user info will be stored and other table is visituser table where if one user view another users profile then there will be an entry in visituser table. Every time if one user visits another users profile then there will be an entry in visituser table. There will be multiple entry for one user visit to another user. My problem is that i've to show the visitor one time with latest visit date. My query is given below =>

SELECT
    a.*, b.onlinestatus,
    b.username,
    b.age
FROM
    tbl_visitprofile a
LEFT JOIN tbl_user b ON a.visitorid = b.id
WHERE
    b. STATUS != '2'
AND a.visitmemberid = '10'
AND a.visitorid NOT IN (
    SELECT
        user_id
    FROM
        tbl_pauseusers
)
AND a.blockstatus = '0'
GROUP BY
    a.visitorid
ORDER BY
    a.id DESC
LIMIT 0,
 12

If i am not using the group by in this query then i am getting the latest visit entry but if i am using group by then i am not getting the latest visit entry. I'd searched alot but didn't find my annswer anywhere. Any help will be appreciated. Thanks in advance

Its not possible to post the sample data. I want to user group by and order by in one mysql query which is not working as per the expectations — user3327608, Jan 09 '17 at 11:50
@user3327608 you must be able to post sample data. make some up. It doesn't have to actually be real data, just something that will show us the issue. Usually when people post SQL questions they show the table structure and some data example. Like I said, you can make up the data as long as it outlines the problem. — SaggingRufus, Jan 09 '17 at 12:16

score 0 · Accepted Answer · answered Jan 09 '17 at 12:28

0

You can achieve this using the MAX group function. I have cleaned up the query a bit but fundamentally this should retain the same logic you had before whilst being slightly more optimisable. Just change a.date_time for whatever the date time field is in your table.

SELECT
    a.visitorid,
    MAX( a.date_time ) AS last_visit_date_time,
    b.onlinestatus,
    b.username,
    b.age,

FROM tbl_visitprofile a

INNER JOIN tbl_user b
    ON b.id = a.visitorid
    AND b.STATUS != '2'

LEFT JOIN tbl_pauseusers p
    ON p.user_id = a.visitorid

WHERE a.visitmemberid = '10'
    AND a.blockstatus = '0'
    AND p.user_id IS NULL

GROUP BY a.visitorid
ORDER BY last_visit_date_time DESC
LIMIT 0 , 12;

This will order the records by the date/time of last visit and return the latest 12.

answered Jan 09 '17 at 12:28

Simon at The Access Group

6,932
22
45

Thanks very much sir. Thanks for your valuable reply. I really appreciate it. I got my solution with your query. Thanks again – user3327608 Jan 09 '17 at 12:41
I am having the same problem and this didn't work for me – Utku Dalmaz Mar 06 '19 at 10:15
@UtkuDalmaz that is not enough information to be able to provide any meaningful help. I suggest raising a new question with full details so that you can actually find a solution instead of simply stating that this didn't work without any context! – Simon at The Access Group Mar 07 '19 at 08:38

group by and order by in mysql query

1 Answers1

Linked