Page 1 of 1

YAAQ (Yet another advanced query)

Posted: Tue Aug 13, 2002 12:07 pm
by Zmodem
Aight. I'm building an inventory tracking system for my IT dept.

I have two tables, one that holds the equipment, and one that keeps track of the history of that equipment.

Each time something happens to a piece of equipment, a history entry is recorded. Meaning, the unique id of the equipment, is placed in the history table, along with time/date stamp, and WHAT exactly happened.

Here is an example of what I'm talking about:

Code: Select all

EQUIPMENT TABLE:
id     name
8        Toshiba Laptop

HISTORY TABLE
id     machine_id     Action
89        8             Assigned to user John
90         8            Placed on shelf after John's departure
Each piece of equipment could have 0, 1, or more history entries.

I want to select ALL peices of equipment, and only the LATEST history item. (if it exists) In this case, where history.id = 90

The query I'm using is much more complicated than this, but this is the section which I'm having problems with. If you need more, let me know.

Thanks for any help guys and gals

--Cheers

Posted: Tue Aug 13, 2002 12:17 pm
by MattF
"SELECT * FROM history WHERE id='90' order by timestamp asc limit 1"

or if you want every one remove the WHERE id='90' bit. Is that what you want?

Posted: Tue Aug 13, 2002 12:36 pm
by Zmodem
Can't use id=90, all I want is the highest number. It might be 90, it might not be. 90 was just in this particular case.

also, i forgot to mention, that i am using ORDER BY to sort the list by something else already. So we can't use that either :/

other ideas though?

Thanks

Posted: Tue Aug 13, 2002 6:56 pm
by phice
SELECT * FROM table ORDER BY id DESC LIMIT 0,1

Posted: Wed Aug 14, 2002 11:31 am
by RandomEngy
Yikes, I see your problem. I've been wresling with this for a while. I do GROUP BY machine_id, and I can get the max timestamp/id from that, but then I can't use that to get the corresponding "action." Also, it seems mysql will only return one row for each unique value in the column you grouped by, preventing the use of HAVING at the end to prune off everything you don't want. After much fiddling, I can't say I've gotten too much figured. :/ My best guess on how to handle this is some kind of funky self-join, but I don't know how to start there.