[SOLVED] Simple table-Getting results using one instead 2 queries

[anthylon]

Hello,

 

I need help with making one query ???. I am not sure is it possible but here you go:

 

my_table

id > number

 

mdate > date

 

 

mval > text

 

 

 

Example (table: my_table):

 

|  id | mdate | mval

443323 | 1/17/2008 | some string value |

 

443323 | 2/19/2004 | some string value |

 

 

443323 | 9/11/2009 | some string value |

 

 

443323 | 5/1/2009 | some string value |

 

443323 | 1/17/2008 | some string value |

 

778337 | 2/19/2004 | some string value |

 

 

778337 | 9/11/2009 | some string value |

 

 

 

No what I need is to search trough database and get the following data for each id:

- id

- mval

- mdate (optional)

with condition MAX(mdate).

 

So, what I really need is to take only id, mval for each id in the table and based on previous sample table the result would be:

 

Example (Required Result):

 

|  id | mdate | mval

 

 

443323 | 9/11/2009 | some string value |

 

 

778337 | 9/11/2009 | some string value |

 

What I am using now is:

- Query 1: select id and MAX(mdate) into another table (example: my_results)

INSERT INTO my_results (id, mdate) SELECT my_table.id, MAX(my_table.mdate) AS mdate
FROM my_table
GROUP BY my_table.id

 

- Query 2: Adding mvalue string to my_results

UPDATE my_results INNER JOIN my_table ON (my_table.id=my_results.id) AND (my_table.mdate=my_results.mdate) SET my_results.mval = my_table.mval

 

Is there any way I could do this using only one query?

 

Thank you!

 

 

[aschk]

Yuck using temp tables...

 

Here's a single query solution:

SELECT t1.*
FROM my_table t1
JOIN
  (SELECT id, MAX(date) AS mdate
   FROM my_table
   GROUP BY id) as t2 ON t1.date = t2.mdate AND t1.id = t2.id

[anthylon]

It works! I just had to make it RIGHT JOIN :-\. The rest of code remain the same:

 

SELECT t1.*
FROM my_table t1
RIGHT JOIN
  (SELECT id, MAX(mdate) AS rdate 
   FROM my_table
   GROUP BY id) as t2 ON t1.mdate = t2.mdate AND t1.id = t2.id

 

 

I appreciate your help !

 

Thank you !

[aschk]

Curiously why did you make it a RIGHT JOIN? There should NEVER be an instance where it can't join to itself based on date and id.

[fenway]

Curiously why did you make it a RIGHT JOIN? There should NEVER be an instance where it can't join to itself based on date and id.

Agreed.

[anthylon]

I am not sure I understand your question. Is that you telling me I should skip RIGHT JOIN?

 

Well, this is quick MS Access task and I had to use it once only. But my supervisor - he is not even programmer - asked me why I made two queries for that "simple" task. If I tell you that I was around 12.5 hrs working at that day it should be enought to explain how tired I was. So I just posted question and left the office.

 

Without right join it didn't work in Access.

 

I hope this is good answer

[fenway]

why not inner?

[anthylon]

Oh I see now :-\ - You have right . But as I said I haven't think about optimisation as this was for single use only . I mean, it was simple Access DB - we used it only to maintain some quick reports - data imported from Excel etc... ??? ??? ???

 

I will do next time - thank you