Results 1 to 15 of 15
  1. #1
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803

    How do I limit my result set to just one record

    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

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803
    Quote 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.

    Code:
    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.
    Last edited by Frunkie; 09-27-07 at 22:32.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    so it's just the latest row per person?
    Code:
    select employee_Id 
         , status  
         , timestamp
      from daTable as T
     where timestamp = 
           ( select max(timestamp)
               from daTable
              where employee_Id = T.employee_Id )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803
    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

  6. #6
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803
    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.

    Code:
    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.

  7. #7
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803
    I believe that I have it. Here is what I have done.

    Code:
    SELECT employee_Number_Id,
         max( timestamp ) as max
      FROM console
    GROUP BY employee_Number_Id
    Is this ok?

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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!

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803
    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.

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803
    Quote 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..


  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803
    Quote Originally Posted by r937
    so it's just the latest row per person?
    Code:
    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

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  15. #15
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803
    Thanks so much, Rudy!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •