SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Member Gina Graham's Avatar
    Join Date
    Jul 2004
    Location
    Fairbanks, Alaska
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Want MAX value for each subset in a table.

    I have a table that contains several instances of each Case ID.

    I have sucessfully gotten a unique CaseID and it's corresponding MAX value for Modified Date. But I want to return the whole record for that instance.

    Here's what I have so far:

    SELECT [Case ID], MAX([Modified Date]) AS [MaxOfModified Date]
    FROM AllEntries2004
    GROUP BY AllEntries2004.[Case ID];

    ...I am working in Access....

  2. #2
    Chessplayer kleineme's Avatar
    Join Date
    Apr 2004
    Location
    Germany
    Posts
    608
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    what kind of data is stored in such a record? Is it the same for every unique CaseID or does it differ?
    Never ascribe to malice,
    that which can be explained by incompetence.
    Your code should not look unmaintainable, just be that way.

  3. #3
    SitePoint Guru
    Join Date
    Jan 2004
    Location
    Uppsala, sverige
    Posts
    700
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)
    Code:
    SELECT [Case ID], 
    [Modified Date] AS [MaxOfModified Date],
    <other columns>
    FROM AllEntries2004 as q
    where [Modified Date] =
    (select max([Modified Date]) 
    FROM AllEntries2004
    where [Case ID] = q.[Case ID])

  4. #4
    SitePoint Member Gina Graham's Avatar
    Join Date
    Jul 2004
    Location
    Fairbanks, Alaska
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you! Thank you! Thank you!

    That got me to where I needed to go!

  5. #5
    SitePoint Member Gina Graham's Avatar
    Join Date
    Jul 2004
    Location
    Fairbanks, Alaska
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Slow query

    That worked great....but boy is it slow.

    Any advise on speeding it up?

    I am trying to write it to a table, but it doesn't seem any faster.

    Here is what I modified from your help.

    (SELECT [Case ID],
    [Modified Date] AS [MaxOfModified Date],
    q.[Badge ID], q.Name, q.Email, q.Phone, q.Office, q.City, q.[Employee Badge ID], etc..... INTO AllUnique2004
    FROM AllEntries2004 AS q
    WHERE [Modified Date] =
    (SELECT MAX([Modified Date])
    FROM AllEntries2004
    WHERE [Case ID] = (q.[Case ID])))

    Thanks,

    Gina

  6. #6
    SitePoint Guru
    Join Date
    Jan 2004
    Location
    Uppsala, sverige
    Posts
    700
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)
    What indexes do you have on your table?
    Which DBMS is this?

  7. #7
    SitePoint Member Gina Graham's Avatar
    Join Date
    Jul 2004
    Location
    Fairbanks, Alaska
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    just an auto number ID.

    I have a bunch of data in spreadsheets that I am trying to clean it up one swoop before I break them apart into

    The problem with cleaning it up in seperate pieces is that there are multiple instances of a record that contains a specific CaseID. And I only want to keep the most recent.


Bookmarks

Posting Permissions

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