mysql query

[chaiwei]

I have a table like this:

job_id, resume_id, status, datestamp

3965 78826 accepted 2009-12-11 16:34:58 3965 79307 accepted 2009-12-17 11:04:36 3965 78826 can-rejected 2010-01-11 11:09:13 3965 56341 suitable 2009-12-03 11:25:58 3965 78826 suitable 2009-12-11 16:34:51 3965 79307 suitable 2009-12-17 11:04:33

I want it become like this

 

job_id, resume_id, status, datestamp

3965	79307	accepted	2009-12-17 11:04:36
3965	78826	can-rejected	2010-01-11 11:09:13
3965	56341	suitable	2009-12-03 11:25:58

 

I can use subquery to do this:

 

SELECT * 
FROM resume_application_status rjs, 
   (SELECT max(datestamp) as datestamp FROM resume_application_status rjs 
    GROUP BY job_id,resume_id ) rjs2 
WHERE rjs.datestamp = rjs2.datestamp 
GROUP BY job_id,resume_id

 

but this is subquery, are there any option to do this without using subquery?

Because I can't create view using subquery

 

[Mchl]

As far as I can tell, you have to use subquery for that

[fenway]

There are unorthodox ways to get that output with ORDER BY, HAVING and DISTINCT, but it's a hack.

[chaiwei]

Hack?  Interesting.

Can you give me an example?

 

I would love to learn more on sql.

Thanks in advance.

[fenway]

Well, DISTINCT happens after the ORDER BY, so in principle, you can "discard" the others.