Post reply

How do I mimic MS Access Group-By First Functionality

  • August 18, 2004 at 7:57 am

    #86324

    I have an app in which values of various factors are stored in a table:

    Date/Time                   Factor                Value
    01/01/2001  10:10.32    F21                  2
    01/01/2001  11:24.32    F21                  1
    01/01/2001  11:25.39    Q4                   23
    01/01/2001  11:29.12    Q4                   65
    01/01/2002  05:32.54    F21                  3
     
    I need to develop a view/sp to retrieve a recordset with only the mot recent values for each unique factor.  I am familiar with how to do this with access (GroupBy - First functionality) but I have not been able to figure this out in SQL Server 2000.
     
    Any assistance would be appreciated!

  • August 19, 2004 at 3:07 am

    #519714

    some thing on the lines of the followng should get you there....

     

    select * from table b inner join

    (select factor, min(date/time) as mindate from table

    group by factor) as a on b.factor = a.factor and b.date/time = a.datetime

     

     

    determine the min date/time for each factor (group by)...and join this result set back to the original data to get the 'extra values' for the minimum record.

  • August 19, 2004 at 9:31 am

    #519837

    Use MAX for most recent.

  • August 19, 2004 at 9:55 am

    #519839

    MAX....

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply