9 replies to this topic

[manishk3008]

I have this database table named messages:

global_ref_id int(12)
to int(12)
from int(12)
message text
status int(1)
viewed int(1)

where global_ref_id is id of messages (foreign key from table events), to is user id of user who received message, from is user id of user who send the message.
I want to generate a query which gives me result displaying latest message from each user to a user (say whose user_id is 192) also giving total number of messages from each user to user 192.
Till now I came up with this query to get results:

SELECT
messages.*,events.event_time, COUNT(messages.from) as "Total number of Messages to User"
FROM
messages, events
WHERE
events.global_id = messages.global_ref_id
AND messages.to = 192
GROUP BY messages.from
ORDER BY events.event_time DESC

now problem is that, this query is displaying the oldest message instead of latest message what I need is Latest message.
Using MYSql version:5.5.25a

For easy understanding I have created table in sql fiddle:

http://sqlfiddle.com/#!2/c68dc/7

[Muddy_Funster]

if the problem is that the oldest date is coming back per group then selecting the MAX() value of Event_Time per the grouping should help, something like this :

SELECT messages.global_ref_id,
messages.`to`,
messages.`from`,
messages.message,
messages.status,
messages.viewed,
latest_events.last_event,
count(*) as `TOTAL NUMBER OF MESSAGES TO USER`
FROM messages INNER JOIN
 (SELECT global_id, MAX(event_time) as last_event FROM events GROUP BY event_creator)
 as latest_events
 ON(messages.global_ref_id = latest_events.global_id)
WHERE messages.`to` = 192
GROUP BY messages.`from`
ORDER BY latest_events.last_event DESC

However, if it's only displaying in the wrong order, change the DESC in the order by to ASC to reverse which way it is being ordered.

[Muddy_Funster]

I only realised after I posted that I could actualy play with your fiddle (I can't be the only one thinking that sounds wrong...). Being able to use your tables and sample data I came up with this:

SELECT messages.global_ref_id,
messages.`to`,
messages.`from`,
messages.message,
messages.status,
messages.viewed,
events.event_time,
count_table.msg_count as `total Number of messages`
FROM
messages
INNER JOIN
(select max(global_ref_id) as ref_id, count(*) msg_count from messages group by `from`)
as count_table
on (messages.global_ref_id = count_table.ref_id)
INNER JOIN events
on (events.global_id = messages.global_ref_id)
where messages.`to` = 192
order by events.global_id DESC

It preserves the overall message count while only showing the most recent record activity. I hope that's what you are after.

[manishk3008]

That worked like charm!!!
There is one more thing I would like to add to query, that if I can get count of messages which have message.status=1 for each user (by this I meant to get count of unread messages) in the query, can I count multiple things in a single query?

Edited by manishk3008, 19 November 2012 - 11:30 AM.

[Muddy_Funster]

Sure you can, to be honest, there isn't a whole lot you can't do with SQL. Give me 10min to have a play with the query again and I'll get back to you (or someone better will have the answer before then...).

[Muddy_Funster]

OK, here you go, this counts the status = 1 as well as the total number of messages. I reffered to it as unread because of what you were saying earlier, but I don't know why you would need a field for status for unread as well as a field for viewed. The key player, asside from the count(*) is the JOIN statement. It's one of the most powerfull tools you get when building queries, you should look into learning more about them if you have the time. Still, here's the code.

SELECT messages.global_ref_id,

messages.`to`,

messages.`from`,

messages.message,

messages.status,

messages.viewed,

events.event_time,

count_table.msg_count as `total Number of messages`,

get_unread.unread_count as `Number of unread messages`

FROM

messages

INNER JOIN

(select max(global_ref_id) as ref_id, count(*) msg_count from messages group by `from`)

as count_table

on (messages.global_ref_id = count_table.ref_id)

INNER JOIN events

on (events.global_id = messages.global_ref_id)



INNER JOIN

(SELECT MAX(global_ref_id) as unread_ref_id, count(*) as unread_count FROM messages WHERE status=1 GROUP BY `from`)

as get_unread

ON (get_unread.unread_ref_id = messages.global_ref_id)



where messages.`to` = 192

order by events.global_id DESC

[manishk3008]

Thanks you build this one perfect too!!!
SQL is like super cool, I really need to do some home work before proceeding with my project.

[Muddy_Funster]

Glad I could help. SQL is a lot more powerfull that it looks on the surface. I wouold also like to say that, for the future, you should get a list of MySQL reserved words and either print it off or bookmark it. These are words that are generaly core commands sich as DATE that, while some front ends will let you use them to create column names in your tables, can cause no end of problems when trying to run queries if you use them outside of their reserved purpose. Good luck, and you know where to find us if you have any more questions.

[manishk3008]

I only realised after I posted that I could actualy play with your fiddle (I can't be the only one thinking that sounds wrong...). Being able to use your tables and sample data I came up with this:

SELECT messages.global_ref_id,
messages.`to`,
messages.`from`,
messages.message,
messages.status,
messages.viewed,
events.event_time,
count_table.msg_count as `total Number of messages`
FROM
messages
INNER JOIN
(select max(global_ref_id) as ref_id, count(*) msg_count from messages group by `from`)
as count_table
on (messages.global_ref_id = count_table.ref_id)
INNER JOIN events
on (events.global_id = messages.global_ref_id)
where messages.`to` = 192
order by events.global_id DESC

It preserves the overall message count while only showing the most recent record activity. I hope that's what you are after.

I tested this query on original database, there is one problem that it shows only 2 results only no matter what should be the number of rows it should return.

[Muddy_Funster]

Then you'll need to let us see a more accurate dataset. if the structure is the same then it must be something to do with how the data is grouped. You did change the where messages.to = to the appropriate value yeah?