CFQUERY help using GROUP BY

[lucidtech]

I am trying to run a query where all messages are pulled form a users inbox. Messages are kept clean in the inbox by setting a 'mail_orig' column in the table to the date of the original message.. this way all correspondences between the two parties concerning this message can easily be displayed on the same page. What I'm trying to do in this query however is pull the LAST message sent for each DISTINCT MAIL_ORIG (yes, I've tried this using the DISTINCT function for SQL as well with the same kind of results) value and ORDER THEM by the MAIL_DATE column, or the date of the latest message. What's happening though is the system is using the date of the FIRST message for each correspondance and ordering them that way. Any ideas?

<cfquery name="get" datasource="ds">
SELECT *
FROM user_mail
GROUP BY mail_orig
WHERE user_id = 1
ORDER BY mail_id DESC
LIMIT #start#, 10
</cfquery>

 

[r937]

SELECT t.*    
  FROM user_mail AS t
INNER
  JOIN ( SELECT mail_orig
              , MAX(mail_date) AS last_date
           FROM user_mail
         GROUP 
             BY mail_orig ) AS m
    ON m.mail_orig = t.mail_orig
   AND m.last_date = t.mail_date
 WHERE t.user_id = 1
ORDER 
    BY mail_id DESC LIMIT #start#, 10

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[lucidtech]

Thank you so much.. tested it and it worked. I'll be breaking to down with some tutorials today to make sure I understand WHY it works... hopefully I won't have any questions.

 

[lucidtech]

I tried it on my local server, worked great. On the remote server, however, where I have a lot of mail, the query ran incredibly slow. A query of query looping through a condition runs a lot faster. Thanks for the help though.

 

[r937]

is there an index on mail_orig? preferably (mail_orig,mail_date)

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[lucidtech]

the index for the mail is mail_id, is set as a primary, unique, auto-incremented integer. The only other thing I can think of to try is a table row that includes both the original date of the messages AND the id of the user who sent them, possibly separated by a ';', so that I can set that column as the index.. this would prevent duplicate entires of data for that column since a user couldn't (unless really really trying) send two messages to the same user at the same exact time.. and perhaps this would decrease query times if I grouped by that column AND it was the index. Any thoughts.

 

[r937]

sounds like mysql

can you do a SHOW CREATE TABLE please

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[lucidtech]

Sorry it took me so long to get back to you r937. Yes, I am using mysql.. honestly when I first started I was using an Access database and quickly learned my lesson. My hosting provider does provide me with use of one MSSQL database. I'm guessing your suggestion would be to migrate?

 

[r937]

no, i am not suggesting you migrate

stick with the database you started with here, just make sure the columns are properly indexed

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[lucidtech]

The mail table is indexed with a 'mail_id' column that is set to primary and auto-increment.

 

[r937]

is there an index on mail_orig? preferably (mail_orig,mail_date)

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon