If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > MySQL > write a right sql statement to get this job done

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old 03-12-10, 11:17
chiefman chiefman is offline
Registered User
 
Join Date: Jan 2009
Posts: 23
write a right sql statement to get this job done

Hi guys, may I have your attention and help on this. Thanks!

The table "message(id, userId, content, createdTime)". now I need to find out those records of the latest message (namely the max createdTime) for each user, the returned results should be ordered by createdTime.
e.g
data in the table:
1 'eric' 'hello' '2009-12-30'
2 'lisa' 'yes' '2010-01-21'
3 'eric' 'thanks' '2009-10-06'
4 'lisa' 'hey' '2010-09-12'

I got one: select msg. * from (select m.* from message as m order by m.createdTime) as msg group by msg.userId order by msg.createdTime DESC. but I think it's not good due to low performance.

the expected results:
4 'lisa' 'hey' '2010-09-12'
1 'eric' 'hello' '2009-12-30'

Last edited by chiefman; 03-12-10 at 11:21.
Reply With Quote
  #2 (permalink)  
Old 03-12-10, 11:28
dav1mo dav1mo is offline
Registered User
 
Join Date: Dec 2007
Location: Richmond, VA
Posts: 782
What's going on? This same type question is popping up on all of the DB boards. The SQL is fairly simple. You want a correlated subquery, where you join the table to itself looking for the max date/time for that person.
Dave
Reply With Quote
  #3 (permalink)  
Old 03-12-10, 11:45
mike_bike_kite mike_bike_kite is offline
vaguely human
 
Join Date: Jun 2007
Location: London
Posts: 2,519
Quote:
Originally Posted by dav1mo View Post
What's going on? This same type question is popping up on all of the DB boards. The SQL is fairly simple.
Lecturer sets homework. Students post their homework on their chosen forums. We spend half our day doing the students homework. Students then finish their course but are mysteriously incapable of doing anything. No one wants to employ them as they know nothing. Parents wonder why they spent their money on the course fees etc.

Does that explain things?

__________________
Mike
Reply With Quote
  #4 (permalink)  
Old 03-12-10, 15:36
r937 r937 is offline
SQL Consultant
 
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,524
Quote:
Originally Posted by mike_bike_kite View Post
No one wants to employ them as they know nothing.
other than this part, your synopsis of the situation is bang on

homework time comes in cycles, and i've noticed on several boards we are in another cycle

the part above isn't quite right -- they ~do~ get hired, and then, from the comfort of a paid job, post questions like "halp!!! how do i remove all but one of the duplicates in my table???"
__________________
r937.com | rudy.ca
please visit Simply SQL and buy my book
Reply With Quote
  #5 (permalink)  
Old 03-12-10, 16:07
Pat Phelan Pat Phelan is offline
Resident Curmudgeon
 
Join Date: Feb 2004
Location: In front of the computer
Posts: 12,605
Now come on Rudy, when Mike wrote that "No one wants to employ them" I think he was spot on in his analysis... That doesn't mean that someone might not be forced to employ them, that's a very different thing!

-PatP
__________________
In theory, theory and practice are identical. In practice, theory and practice are unrelated.
Reply With Quote
  #6 (permalink)  
Old 03-12-10, 16:20
r937 r937 is offline
SQL Consultant
 
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,524
forced? how forced?

anyhow, my point was that the employer might not know that the candidates are under-qualified, simply by virtue of the fine marks that they pulled off for their assignments

of course, rigourous testing as part of the interview process should take care of that...
__________________
r937.com | rudy.ca
please visit Simply SQL and buy my book
Reply With Quote
  #7 (permalink)  
Old 03-12-10, 23:19
Pat Phelan Pat Phelan is offline
Resident Curmudgeon
 
Join Date: Feb 2004
Location: In front of the computer
Posts: 12,605
I'll give you a call to share some horror stories about our recent interviews. You are hands down a better administrator than some of the folks that have presented resumes lately.

-PatP
__________________
In theory, theory and practice are identical. In practice, theory and practice are unrelated.
Reply With Quote
  #8 (permalink)  
Old 03-13-10, 03:42
chiefman chiefman is offline
Registered User
 
Join Date: Jan 2009
Posts: 23
Quote:
Originally Posted by mike_bike_kite View Post
Lecturer sets homework. Students post their homework on their chosen forums. We spend half our day doing the students homework. Students then finish their course but are mysteriously incapable of doing anything. No one wants to employ them as they know nothing. Parents wonder why they spent their money on the course fees etc.

Does that explain things?

I am shamed at the simple sql question. right, your words do mean something to me. to be honest, I am fresh to such stuff like database. I sense that you all are experts. Anyway, thank you
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On