grey580 Posted February 27, 2007 Share Posted February 27, 2007 I'm making a system for keeping track of whether or not people are at their desks, on the phone, out smoking, in the bathroom, not at their desks, in a meeting etc etc etc..... currently i'm using select rep_id, date from rep_status order by rep_id, date desc; I'm just getting all the entries and then using the first one/latest one from NOW() for my results. however I'd like to do a better query that only gets the 1 result i need from the database instead of everything for the various users. Quote Link to comment Share on other sites More sharing options...
artacus Posted February 27, 2007 Share Posted February 27, 2007 That sounds like a great idea. Go for it. Quote Link to comment Share on other sites More sharing options...
grey580 Posted February 27, 2007 Author Share Posted February 27, 2007 well yeah for now. but that tables is going to fill up. and i don't want to be pulling down thousands of records after a year. it would kind of be a waste. anyone know of a better way to do this? Quote Link to comment Share on other sites More sharing options...
artacus Posted February 27, 2007 Share Posted February 27, 2007 LIMIT 1 Quote Link to comment Share on other sites More sharing options...
grey580 Posted February 27, 2007 Author Share Posted February 27, 2007 that doesn't help. that only returns one entry. I want to get only 1 entry from the entries of multiple users. I'd like to do that without having to do multiple mysql queries. there are about 20+ users that i want results for. I want the latest results. table looks like this. +----+---------------------+--------+--------+ | id | date | rep_id | status | +----+---------------------+--------+--------+ | 7 | 2007-02-27 15:05:48 | albert | 4 | +----+---------------------+--------+--------+ Quote Link to comment Share on other sites More sharing options...
fenway Posted February 27, 2007 Share Posted February 27, 2007 You mean the most recent per user? select rep_id, MAX(date) from rep_status group by rep_id order by rep_id Quote Link to comment Share on other sites More sharing options...
grey580 Posted February 28, 2007 Author Share Posted February 28, 2007 nice! that's getting closer to my goal. however i also need to get the status to go along with that date. right now if i try running that query to also grab the status i get a random one. Quote Link to comment Share on other sites More sharing options...
fenway Posted February 28, 2007 Share Posted February 28, 2007 That's correct -- use this as a derived table, and join it back to the rep_status group via the "matching' rep_id, and then grab whatever other data you need. Quote Link to comment Share on other sites More sharing options...
grey580 Posted February 28, 2007 Author Share Posted February 28, 2007 ok now you lost me. could you please show me an example. Quote Link to comment Share on other sites More sharing options...
fenway Posted February 28, 2007 Share Posted February 28, 2007 Since, as you discovered, you get back random columns for non-grouped by fields with GROUP BY, you need to query to original table again, using the UIDs that you want: SELECT * FROM rep_status AS r INNER JOIN ( select rep_id, MAX(date) from rep_status group by rep_id order by rep_id ) AS sub ON ( sub.rep_id = r.rep_id ) Quote Link to comment Share on other sites More sharing options...
grey580 Posted February 28, 2007 Author Share Posted February 28, 2007 hrmmm... actually it's not pulling a random value. it's pulling the first value it can find. i wonder if there is a way it could pull/give me the last value it found/more recent one. at this rate it looks like i'm just going to run 2 queries one getting the max date. then running another query grabbing those max dates from the database and shoving them into an array. that one query you gave me this result. +----+---------------------+--------+--------+--------+---------------------+ | id | date | rep_id | status | rep_id | MAX(date) | +----+---------------------+--------+--------+--------+---------------------+ | 1 | 2007-02-26 16:32:14 | albert | 1 | albert | 2007-02-28 09:05:48 | | 2 | 2007-02-27 11:25:46 | albert | 2 | albert | 2007-02-28 09:05:48 | | 5 | 2007-02-27 14:50:11 | albert | 3 | albert | 2007-02-28 09:05:48 | | 7 | 2007-02-28 09:05:48 | albert | 4 | albert | 2007-02-28 09:05:48 | | 8 | 2007-02-28 10:11:11 | jack | 1 | jack | 2007-02-28 10:11:11 | | 3 | 2007-02-26 11:29:46 | kevin | 2 | kevin | 2007-02-27 14:52:09 | | 4 | 2007-02-25 11:29:46 | kevin | 1 | kevin | 2007-02-27 14:52:09 | | 6 | 2007-02-27 14:52:09 | kevin | 3 | kevin | 2007-02-27 14:52:09 | +----+---------------------+--------+--------+--------+---------------------+ what i really would like to see is this. +--------+---------------------+--------+ | rep_id | date | status | +--------+---------------------+--------+ | albert | 2007-02-28 09:05:48 | 4 | | jack | 2007-02-28 10:11:11 | 1 | | kevin | 2007-02-27 14:52:09 | 3 | +--------+---------------------+--------+ if you have any more suggestions i'd sure appreciate it. thanks for the help. Quote Link to comment Share on other sites More sharing options...
fenway Posted February 28, 2007 Share Posted February 28, 2007 It does get the first one it can find, but that can change, it's totally unpredictable. And it's giving you everything because I used "*"... just ask for the columns you want. And in my haste, I joined the tables backwards: SELECT r.rep_id, sub.date, r.status FROM ( select rep_id, MAX(date) AS date from rep_status group by rep_id order by rep_id ) AS sub INNER JOIN rep_status AS r ON ( r.rep_id = sub.rep_id ) Hope that helps. Just note that this wil only pull rep_ids with status records. Quote Link to comment Share on other sites More sharing options...
grey580 Posted February 28, 2007 Author Share Posted February 28, 2007 well I am only looking for reps with entries anyways. but the code below outputs alot of data. I really only need the latest entry from each user. Good stuff though. I'm learning here mysql> SELECT r.rep_id, sub.date, r.status FROM -> ( select rep_id, MAX(date) AS date from rep_status group by rep_id order by rep_id ) AS sub -> INNER JOIN -> rep_status AS r -> ON ( r.rep_id = sub.rep_id ); +-----------+---------------------+--------+ | rep_id | date | status | +-----------+---------------------+--------+ | albert | 2007-02-28 12:47:25 | 1 | | albert | 2007-02-28 12:47:25 | 2 | | kevin | 2007-02-27 14:52:09 | 2 | | kevin | 2007-02-27 14:52:09 | 1 | | albert | 2007-02-28 12:47:25 | 3 | | kevin | 2007-02-27 14:52:09 | 3 | | albert | 2007-02-28 12:47:25 | 4 | | jack | 2007-02-28 10:11:11 | 1 | | albert | 2007-02-28 12:47:25 | 5 | | albert | 2007-02-28 12:47:25 | 6 | | jeffscott | 2007-02-28 12:42:41 | 5 | | albert | 2007-02-28 12:47:25 | 5 | | albert | 2007-02-28 12:47:25 | 1 | +-----------+---------------------+--------+ Quote Link to comment Share on other sites More sharing options...
grey580 Posted February 28, 2007 Author Share Posted February 28, 2007 the eureka moment kicked in. I changed your statement around a bit and i got this. SELECT r.rep_id, r.date, r.status FROM ( select rep_id, MAX(date) AS tempdate from rep_status group by rep_id order by rep_id ) AS sub INNER JOIN rep_status AS r ON ( r.date = sub.tempdate ); which now gives me this +-----------+---------------------+--------+ | rep_id | date | status | +-----------+---------------------+--------+ | kevin | 2007-02-27 14:52:09 | 3 | | jeffscott | 2007-02-28 12:42:41 | 5 | | albert | 2007-02-28 12:47:25 | 8 | | maria | 2007-02-28 16:30:21 | 9 | | jack | 2007-02-28 16:35:09 | 2 | +-----------+---------------------+--------+ which is what i was looking for... Thanks for the help! Quote Link to comment Share on other sites More sharing options...
fenway Posted March 1, 2007 Share Posted March 1, 2007 Actually, you need both, because the dates could accidentally be shared amongst rep_ids. And yes, I forgot about the date column, my bad. SELECT r.rep_id, sub.date, r.status FROM ( select rep_id, MAX(date) AS date from rep_status group by rep_id order by rep_id ) AS sub INNER JOIN rep_status AS r ON ( r.rep_id = sub.rep_id AND r.date = sub.date ) Be sure you have the right colmnns indexed! Quote Link to comment Share on other sites More sharing options...
grey580 Posted March 1, 2007 Author Share Posted March 1, 2007 awesome. I'll give that a try. thanks for the help. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.