mySQL query question

[Jon Braunsma]

I have a database with a master - detail relationshio between two tables. The database is basically a snapshot in time for each office. so imagine that my master table is: id (auto increment), date, office_id 1 , 2010-4-1, 100 2 , 2010-4-2, 100 3 , 2010-4-3, 100 4 , 2010-4-4, 100 5 , 2010-4-1, 101 6 , 2010-4-2, 101 7 , 2010-4-3, 101 8 , 2010-4-4, 101 I want to get the id number for the last update date from the master table so I did the following: SELECT id, max(upload_date) as upload_date, office_id from master group by office_id What I want is the id number associated with the latest date, so I WANT id, date, office 4, 2010-4-4,100 8, 2010-4-4,101 What I get is: 1, 2010-4-4,100 5, 2010-4-4,101 I get the max date, but the first id number. Normally I'd do a cursor in a procecure to get it, but the ISP has to grant the pivs each time the sp is modified so I am trying to do this in a simple query since it is called from php. As a work around, I am doing a max(id) as well since I figured that the highest id would always be associated with the latest date, but I don't want to make that assumption. Does anyone know how to build the query correctly to get the id associated with the latest date?

[Luc Pattyn]

I'm not sure, I think you need to add some sorting, try "ORDER BY date DESC" on top of your grouping. :)

Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles]

---

Prolific encyclopedia fixture proof-reader browser patron addict?
We all depend on the beast below.

---

[Md Marufuzzaman]

You can try SQL SCOPE_IDENTITY function to get the last identity value inserted into an identity column in the same scope. Or you can also try in a trigger which something like:

DECLARE @id int = 0
SET @id = (SELECT id FROM INSERTED )

Thanks Md. Marufuzzaman

---

I will not say I have failed 1000 times; I will say that I have discovered 1000 ways that can cause failure – Thomas Edison.

[Md Marufuzzaman]

I'm little bit confused; are we trying to get the current inserted / updated data using the dateTime criteria? :confused:

Thanks Md. Marufuzzaman

---

I will not say I have failed 1000 times; I will say that I have discovered 1000 ways that can cause failure – Thomas Edison.

[Mycroft Holmes]

I wasn't sure what MySQL supports so I kept out of it!

Never underestimate the power of human stupidity RAH

[Luc Pattyn]

No, I was suggesting to get things sorted within each group, but MySQL isn't accepting a "ORDER BY ... GROUP BY ..." qualification. :)

Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles]

---

Prolific encyclopedia fixture proof-reader browser patron addict?
We all depend on the beast below.

---

[Md Marufuzzaman]

Yep, I got your point. :)

Thanks Md. Marufuzzaman

---

I will not say I have failed 1000 times; I will say that I have discovered 1000 ways that can cause failure – Thomas Edison.

[Chris Meech]

Try the following

select a.id
,a.upload_date
,a.office_id
from ( select max(m.upload_date) max_upload_date
,m.office_id office_id
from master m
group by m.office_id
) tmp
,master a
where a.upload_date = tmp.max_upload_date
and a.office_id = tmp.office_id
;

You need to be joining the table on itself, though it is more a view on itself. :)

Chris Meech I am Canadian. [heard in a local bar] In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]