GROUP BY and ORDER BY.... the latter not working

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

GROUP BY and ORDER BY.... the latter not working

Post by simonmlewis »

I am creating a little messages system.

The screen should show a grouped list of messages from the database. Each message is grouped by the threadid. But I need the message to show in the main window, the last message that was sent for that threadid.

Right now it's always only shown the 'first' message that was sent.

I have tried to turn around the ORDER BY so that comes before the GROUP BY but it doesn't seem to work.

What I am doing wrong?
Attachments
Screenshot to show the issue
Screenshot to show the issue
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: GROUP BY and ORDER BY.... the latter not working

Post by simonmlewis »

Answered my own question here - sorry. Unless there is a solution to literally show the last ROW, this shows whatever is found (as they are all the same, bar the communication field):

Code: Select all

$query = "SELECT threadid, max(datesent) as datesent, id, emailfrom, emailto, reason, communication, productid FROM messages WHERE emailto = :cookieid  GROUP BY threadid ORDER BY datesent DESC";
Is it possibly tho to group by and then order by???
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: GROUP BY and ORDER BY.... the latter not working

Post by simonmlewis »

The above won't work now either as when a message is read the first time, I am enter a 1 in the messageread field.

Code: Select all

$query = "SELECT threadid, max(datesent) as datesent, messageread, id, emailfrom, emailto, reason, communication, productid FROM messages WHERE emailto = :cookieid  GROUP BY threadid ORDER BY datesent DESC";
So I really need to ORDER the row found by 'datesent', so I can then run a simple query on messageread.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: GROUP BY and ORDER BY.... the latter not working

Post by requinix »

Not sure why you're doing a GROUP BY but I'll bet you shouldn't.

So what all are you trying to get? All the messages? Just the one most recent message? You say you want a "grouped list of messages" (like you want all the messages) but also that "the message" shows in some place (like you only want the most recent)... Or maybe you want all of them and then duplicate the most recent somewhere else on the page?
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: GROUP BY and ORDER BY.... the latter not working

Post by simonmlewis »

I want to show the most recent of each "group" of messages/rows. So if there are 5 with threadid 123, I want the latest one to be picked up. That's why I did Group By and Order By.

I would do DISTINCT, and LIMIT it, but I need more than one field to be output.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: GROUP BY and ORDER BY.... the latter not working

Post by requinix »

GROUP BY is for aggregate information. You want a very specific message for each thread, not an aggregation of all messages in a thread. The fact that GROUP BY will return one row per group makes it easy to misuse.

I think of it less like getting the most recent message for each group of messages but instead finding a message for each thread where there isn't anything newer.

Code: Select all

SELECT m1.threadid, m1.datesent, m1.messageread, m1.id, m1.emailfrom, m1.emailto, m1.reason, m1.communication, m1.productid
FROM messages m1
LEFT JOIN messages m2 ON
	m1.threadid = m2.threadid /* same thread */
	AND m2.datesent > m1.datesent /* newer */
WHERE m2.id IS NULL /* nothing from the same thread and newer */
ORDER BY m1.datesent DESC
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: GROUP BY and ORDER BY.... the latter not working

Post by simonmlewis »

I've had to alter it slightly, so only those who have sent or received a message see the result.

However, it now produces 2 rows. There are 5 rows in the DB where their ID is in the system.

Code: Select all

$query = "SELECT m1.threadid, m1.datesent, m1.messageread, m1.id, m1.emailfrom, m1.emailto, m1.reason, m1.communication, m1.productid
FROM messages m1
LEFT JOIN messages m2 ON
   m1.threadid = m2.threadid /* same thread */
   AND m2.datesent > m1.datesent /* newer */
WHERE m2.id IS NULL AND (m1.emailto = :cookieid OR m1.emailfrom = :cookieid) /* nothing from the same thread and newer */
ORDER BY m1.datesent DESC";
The two rows it picks out are the top two. Whether the user sent or received them.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
Post Reply