Results 1 to 15 of 15
  1. #1
    Join Date
    Feb 2004
    Posts
    29

    Unanswered: Need Query for Latest Time per ID

    I've been baffled over how to do this without using a script... I would like to get the info I need with a single query.

    Here's my scenario, the table looks like this (simplified):

    ID, TIME
    101, 5am
    101, 6am
    104, 5am
    260, 5am
    104, 6am
    260, 6am
    101, 7am
    260, 9am
    104, 7am
    101, 8am

    So basically I have a column of identifications and a column of times. They won't necessarily be in order. I would like a query that gives me this:

    101, 8am
    104, 7am
    260, 9am

    It would order the IDs ascending, only showing the newest time assigned to that ID in the table.

    Thanks in advance for any help

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    select id
         , time
      from daTable as T
     where time
         = ( select max(time)
               from daTable
              where id = T.id )
    order
        by id
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Dec 2004
    Posts
    46
    select, id, max(time)
    from daTable
    group by id
    order by id

  4. #4
    Join Date
    Feb 2004
    Posts
    29
    Thanks guys - I used the example that ronin gave because it looked simpler, and it works great.

    Now I have to add to it. I would have posted this last night but I forgot about it. I have another table that associates IDs with NAMEs. So it looks like this:

    101, Apple
    104, Orange
    260, Banana

    What I would like to do is replace the ID with the NAME, so my final query result looked like this:

    Apple, 8am
    Orange, 7am
    Banana, 9am
    Last edited by RickW; 06-12-06 at 10:22.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    if you only want to return those two columns, use the query in post #3

    if there are any other columns in the table, and you want the entire row that has the latest date, use the query in post #3
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    What, you don't like your own solution?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Feb 2004
    Posts
    29
    Quote Originally Posted by r937
    if you only want to return those two columns, use the query in post #3

    if there are any other columns in the table, and you want the entire row that has the latest date, use the query in post #3
    I think there is a typo, both your statements say use #3. I edited my post, I ended up using #3 - now I have something to add to the query. Then after that I think I'm done with it.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    thanks guys, yeah, that was a typo -- if you want other columns in the same row with the latest date, post #2 is the only way to get them correctly
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Feb 2004
    Posts
    29
    Quote Originally Posted by r937
    thanks guys, yeah, that was a typo -- if you want other columns in the same row with the latest date, post #2 is the only way to get them correctly
    My table only has those two columns so it works out fine - but in the future I'm sure I will need your example for other larger tables.

    Can you help with replacing the ID with the NAME? thanks

    edit:
    Okay I got it. My final query looks like this:

    SELECT idTable.name, MAX(timeTable.time)
    FROM time INNER JOIN
    idTable ON timeTable.time = idTable.name
    GROUP BY idTable.name
    ORDER BY idTable.name

    Thanks for all your help.
    Last edited by RickW; 06-12-06 at 11:40.

  10. #10
    Join Date
    Feb 2004
    Posts
    29
    Okay, now what I want to try is instead of getting the most recent timestamp using MAX - is it possible to get any that are newer than 9 minutes?

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    This is pretty basic stuff, Rick. You need to review the information on SELECT statements, WHERE clauses, and AGGREGATE functions in Books Online, as well as become familiar with the various built-in datetime functions.
    Code:
    SELECT	idTable.name,
    		timeTable.time
    FROM	time INNER JOIN
    		idTable ON timeTable.time = idTable.name
    WHERE	timeTable.time > dateadd(minute, -9, getdate())
    ORDER BY idTable.name
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  12. #12
    Join Date
    Feb 2004
    Posts
    29
    Quote Originally Posted by blindman
    This is pretty basic stuff, Rick. You need to review the information on SELECT statements, WHERE clauses, and AGGREGATE functions in Books Online, as well as become familiar with the various built-in datetime functions.
    Code:
    SELECT	idTable.name,
    		timeTable.time
    FROM	time INNER JOIN
    		idTable ON timeTable.time = idTable.name
    WHERE	timeTable.time > dateadd(minute, -9, getdate())
    ORDER BY idTable.name
    Basic stuff to someone that has dealth with it on a semi-regular basis. :P I'm pretty good with Access, but direct sql is still fairly new to me. I took classes is db theory before but still need more time hands-on.

    In this scenario is WHERE better than HAVING?

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by RickW
    In this scenario is WHERE better than HAVING?
    oh yes, oh my goodness, yes

    the fact that Access routinely uses a HAVING clause in places where it logically should be using a WHERE clause does not make it right
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  14. #14
    Join Date
    Feb 2004
    Posts
    29
    Quote Originally Posted by r937
    oh yes, oh my goodness, yes

    the fact that Access routinely uses a HAVING clause in places where it logically should be using a WHERE clause does not make it right
    Okay - I used what blindman gave me, that works great. The only reason I asked about HAVING versus WHERE is because I was trying to search google for help/examples on this before asking you guys, and I was finding stuff that preferred HAVING but the examples didn't quite what I needed.

    thanks again My query is finally complete, I'll let you guys go back to helping others for awhile.

  15. #15
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    HAVING is only meant to be used for criteria involving aggregate functions.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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