[Frunkie]

Hello everyone,

This seems like a simple question but I just can't seem to get past how to solve the problem of limiting my result set to just one "current" record.

I have a status table that holds values for a person's *current* status such as busy, with a client, whatever. A current record means the user's current status and not the past status.

When I select the person's status WHERE user_Id = something, I am getting ALL of thier status records.

How can I get just the *current* record? I'm sure that this is an easy question to answer, but I'm having a mental block.

I was thinking since I am using a timestamp in the table that maybe I could use a CURRENT_TIME() - 1 minute or something like that to get the user's current status?

Can someone please help? Thanks,

Frank

[r937]

which one is the current one? how do you tell a current one from past ones?

are you going to return one row from the query (with person id as parameter), or one row for every person?

[Frunkie]

Originally Posted by r937

which one is the current one? how do you tell a current one from past ones?

are you going to return one row from the query (with person id as parameter), or one row for every person?

Thanks for the help Rudy.

What I would like is to be able to tell what a single person's status is at whatever time of the day it is. So, if there is 1 employee, with 100 records there should be 100 rows reflecting their status. I am only concerned with their "last" or most current entry denoted by the timestamp being most current. The tricky part for me is eliminating the "old" or "history" records. Let me give you some sample data.

employee_Id	status		timestamp
1		in office	2007-01-01 14:36
1		lunch		2007-01-01 15:00:21

There will probably be several rows for employee_Id 1 but what I need is to determine the "latest" entry probably determined by the timestamp which in this case would be the lunch entry at 15:00:21 hours. I don't care about the in office status at 14:36 hours because there is now a new entry at 15:00.

[r937]

so it's just the latest row per person?

select employee_Id 
     , status  
     , timestamp
  from daTable as T
 where timestamp = 
       ( select max(timestamp)
           from daTable
          where employee_Id = T.employee_Id ) 

[Frunkie]

Well, let me try this again seeing as how I already responded once but lost the message due to the site being under maintenance for replication.

Thank you Rudy. The MAX aggragate function worked out very well.

Frank

[Frunkie]

The MAX() function works dandy for one record (As I know is what I said I needed) but I need the top or latest record for each row of data. What constitutes a row of data is the employee number.

Here is some actual sample data from the database.

status	emp_No		timestamp		id
In	1		2007-09-27 08:27:15	1
In	2		2007-09-27 08:45:40	2
In	5		2007-09-27 09:01:10	3
In	3		2007-09-27 09:15:06	4
Busy	1		2007-09-27 10:31:08	5

There are 4 seperate employees. Employee #1 has two entries. I need to get the most recent row for him.

I should have 3 additional rows returned for employee#s 2, 3 and 5 because they only have 1 current entry. If those employees had additional rows, I would need the latest rows returned for those as well.

Is this possible in sql or better done in my application code?

Thanks.

[Frunkie]

I believe that I have it. Here is what I have done.

SELECT employee_Number_Id,
     max( timestamp ) as max
  FROM console
GROUP BY employee_Number_Id

Is this ok?

[r937]

you don't have what you originally asked for, but you do have something

you have the max timestamp per employee

but it is not the row with the max timestamp, because you aren't returning the row, just the PK and the max timestamp

if that's what you wanted, fine, but you did ask to return the row

should you ever wish to have the row with the max timestamp, please see post #5

in particular, you are still missing the status -- you may have the latest timestamp per employee, but you don't know what the status is that corresponds to that!

[Frunkie]

Rudy, thank you! That (post #4) did work after all. I don't know what I did wrong with that code the first time, but I have it working now.

What was confusing me was that I needed to join a second table for additional information and I wasn't quite sure how to do that. I found a few examples after googling it and eventually got it.

Thank you again.

[r937]

just a tip, but if you pose a problem, and you do it in a simplistic way, and then hope to adapt the given solution by adding another table, maybe you should show the real problem right from the get-go

what was the additional table?

[Frunkie]

Originally Posted by r937

just a tip, but if you pose a problem, and you do it in a simplistic way, and then hope to adapt the given solution by adding another table, maybe you should show the real problem right from the get-go

what was the additional table?

I'm so afraid your gonna rip my design apart when I post the whole thing. Actually, I just figured that it would be less confusing if I only posted the table that was giving me a problem. I'll take your advice and in the future post all of the tables involved.

The other table was the employee table.

Since you asked and because I have a headache from overthinking a problem I have been having, please let me ask you how you would handle it.

This other table (employee) holds all of the information on the employee such as name address dob etc. I have two types of employees, office personnel and field personnel. I want to track the whereabouts of both types of employees. I created a springboard type table to show the employees status.

Anybody working inside the office is not a problem. The problem is with the field employees. The field employees require consistant updating of the database. Their location will change several times during a shift.

I have created another table that will give the status of the field personnel and their work and also their whereabouts. There is no need to track work progress for the office personnel.

Idealy, what I was shooting for was to have just one table to put this information into and display it in a tabular format. I couldn't get the relationships correct though. The PK from the field personnel table becomes a FK in the employee status table. The employee PK becomes a FK also in the employee status table.

With regard to the employee status table, I now have two FKs. Whenever I need to update a particular employee, be it office or field, one of the db fields (FK) would have to be nullable which is not correct. I am either updating one type of employee or the other but never both at the same time.

What I have done in the meantime which doesn't seem like a bad alternative was to program two seperate pages; one for field personnel and the other for office personnel. This way, I could just leave the tables as they are.

How might you handle this Rudy?

Thanks..

[r937]

i'm not sure how i would handle it because i don't know what your requirements are

if you are building tables, though, then you are in a much better position than someone who has to retrieve meaningful information from an already-designed database

but it sounds like you have an employees table and an employee status table, and they can be combined easily even with the "only the latest" condition

[Frunkie]

Originally Posted by r937

so it's just the latest row per person?

select employee_Id 
     , status  
     , timestamp
  from daTable as T
 where timestamp = 
       ( select max(timestamp)
           from daTable
          where employee_Id = T.employee_Id ) 

Rudy, this code is really great, thank you! Can you explain it to me please? I understand the select columns, I understand the FROM, but the subquery has me baffled. Would you mind explaining it?

I have to use this code elsewhere in the db but only this time I have to join one other table. How would I go about doing that?

What I need in this query is exactly like before. I need all of the rows returned that have the MAX() timestamp *per person*. I need to join the employee table to pull the person's name.

Thanks,

Frank

[r937]

select T.employee_Id 
     , T.status  
     , T.timestamp
     , employees.name
  from daTable as T
inner
  join employees 
    on employees.id = T.employee_Id 
 where timestamp = 
       ( select max(timestamp)
           from daTable
          where employee_Id = T.employee_Id ) 

the subquery is a correlated subquery, you can tell because it refers to a column, T.employee_Id, which is part of the main query

what that means is, the subquery is evaluated for each row of the main query

for each row of the main query, all the rows which are for the same employee are evaluated

the subquery will retain all rows from the main query that have the highest timestamp for that employee

[Frunkie]

Thanks so much, Rudy!