here ya go –
SELECT d.title
, d.details
, MAX(r.time_posted) AS latest
FROM replies AS r
INNER
JOIN discussion AS d
ON d.id = r.qa_id
WHERE r.user_id = 210937
GROUP
BY d.title
, d.details
ORDER
BY latest DESCOMG Man. You got it r937 
You are the MySQL Guru man
I mean I am real good with mySQL commands, but you are above and beyond. Cool
r93, actually there is a Lil problem still.
That is if the user has posted more than 1 reply on a given day, then the above query does not list their latest discussion to which they posted the latest reply to above the discussions to which he/she posted earlier replies to. That is the discussions are sorted correct based on his/her replies posted on daily basis, but NOT within the same day not.
And I tried this and still no good:
replace: MAX(r.time_posted) AS latest
with: MAX(r.id) AS latest
i know this is English but i cannot understand it at all
unless you can explain it better, i’m afraid i’m going to want to see comprehensive test data which demonstrates your problem
So we want to List all the discussions to which a member has posted a reply to, sorted by those discussions to which he/she has posted the latest replies to.
But if the user has posted many replies in a given Day, then the above Query does NOT list the discussions to which he/she has posted replies to based on the latest discussions to which he/she had posted replies to.
It does so when days replies are posted are different, but NOT when the day of the replies are same.
Keep in mind that replies are timestamped, so they are recorded not just by day/month/year but also by Minute & Second of when they were posted.
I hope that makes it as clear as can be.
nope
perhaps you do not understand how MAX() works
each discussion that a user has replied to is included, yes?
MAX() returns the latest reply, no matter if there’s more than one per day
and now, it really is time for you to post comprehensive sample data
2 Likes
Well to see comprehensive sample data, is not so easy, cause:
1- you need to become Anoox member. This is free and takes less than a minute.
2- But then you need to post a whole bunch of answers to member questions & discussions and then check under: “My Conversations” to see this sample data
Because what is interesting, is that IF the member has posted a few messages, then their “My Conversations” will list correctly those discussions that they have posted a reply to based on the discussions to which they have posted latest reply to. But if they have posted many replies, such as 100s and 1000s then it breaks down!
FYI: i have found a 100% solution to this using MySQL & Php. But MySQL alone does not produce 100% accurate data!!!
And FYI we are currently at this MySQL query to get this done:
SELECT d.id, d.user_id, d.date_added, d.head_line, d.viewed, d.category, MAX(r.id) AS latest
FROM replies AS r
INNER JOIN discussions AS d
ON d.id = r.qa_id
WHERE r.user_id = $user_id
GROUP BY d.id
ORDER BY latest DESC
this depends on who writes the queries and their skill level
yup, and i’m saying it gives you waht you asked for
comprehensive test data, please
you don’t have to use live data, but you do have to provide representative data in those tables which will show that the query isn’t working
otherwise, i’m done here
See this screen shot:
As you can see, hopefully you can, this member posted their latest reply to:
#6 : [It is safe to invest in Cryptocurrencies such as BitCoin?]
at 32 minutes 9 seconds ago
However, this discussion is being listed as number 6 in the list of their conversations and not as #2, which has a reply from them to:
[Is it rude to ask a girl on a 1st date that we go Dutch?]
which they posted a reply to: 38 minutes 18 seconds ago
An easy way to help others to be able to help is to post something like this that can be run in the console window:
CREATE TABLE test_discussions (
// definition here
CREATE TABLE test_replies (
// definition here
INSERT INTO test_discussions
( // columns )
VALUES ( // values ) , ( // values ) , ( // values )
INSERT INTO test_replies
( // columns )
VALUES ( // values ) , ( // values ) , ( // values )
SELECT // your query
DROP TABLE test_discussions, test_values
No need for it to have a lot of values or for them to be real. Only that there is enough to demonstrate the problem and they are representative.
yup, looks like a problem
are you claiming that my query produced those results?
Well your query exactly as you gave, was producing little worse results.
So I modified it to this:
SELECT d.id, d.user_id, d.date_added, d.head_line, d.viewed, d.category, MAX(r.id) AS latest
FROM replies AS r
INNER JOIN discussions AS d
ON d.id = r.qa_id
WHERE r.user_id = $user_id
GROUP BY d.id
ORDER BY latest DESC
r9, I have found the problem. Well the solution
Lil convoluted to explain, but above MySQL code is actually fine.
Thanks for your help in this matter
This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.