YAAQ (Yet another advanced query)

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Zmodem
Forum Commoner
Posts: 84
Joined: Thu Apr 18, 2002 3:59 pm

YAAQ (Yet another advanced query)

Post 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
MattF
Forum Contributor
Posts: 225
Joined: Sun May 19, 2002 9:58 am
Location: Sussex, UK

Post 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?
Zmodem
Forum Commoner
Posts: 84
Joined: Thu Apr 18, 2002 3:59 pm

Post 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
User avatar
phice
Moderator
Posts: 1416
Joined: Sat Apr 20, 2002 3:14 pm
Location: Dallas, TX
Contact:

Post by phice »

SELECT * FROM table ORDER BY id DESC LIMIT 0,1
Image Image
User avatar
RandomEngy
Forum Contributor
Posts: 173
Joined: Wed Jun 26, 2002 3:24 pm
Contact:

Post 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.
Post Reply