+ Reply to Thread
Results 1 to 3 of 3
  1. Junior Member Registered Member
    Join Date
    Jan 2011
    Posts
    1
    #1

    Default getting most recent row : date & time separate

    Hi
    My Informix system has a table defined like the following :
    F1 7 character reference
    F2 3 character status
    F3 Date
    F4 Time

    There can be lots of rows for each reference, with differing values for status, date and time. I need a select command which will only return the most recent row, as far as date & time is concerned, but cannot work out how to do this.
    Many thanks in anticipation !
    Reply With Quote Quote  

  2. SS -->
  3. BOBBY_TABLES RobertKaucher's Avatar
    Join Date
    Dec 2007
    Location
    Lebanon, Ohio - USA
    Posts
    4,274

    Certifications
    MCSD Web Apps/SharePoint Applications, MCITP: DBA 2005/2008, EA, EDA7, Linux+, Sec+, MCSE, MCDST, MCTS
    #2
    Always let us know the version of SQL Server and post the DDL and some sample data. I'm making some gueses here, but this should get you started.

    Code:
    DECLARE @A TABLE(
    f1 VARCHAR(7)
    ,f2 VARCHAR(3)
    ,f3 VARCHAR(10)
    ,f4 VARCHAR(8)
    )
    INSERT @A
    SELECT 'field1','f2','20110119','14:00:00' UNION ALL
    SELECT 'field1','f2','20110119','15:00:00' UNION ALL
    SELECT 'field1','f2','20110119','15:15:00' UNION ALL
    SELECT 'field1','f2','20110119','13:00:00'
    
     
    ;WITH dateSelect_CTE AS(
    
     SELECT f1
               ,f2
               ,f3+' '+f4 AS DateField
        FROM @A
    )
    SELECT TOP 1
       f1
      ,f2
      ,CAST(DateField ASDateTime) AS DateField
    
    FROM dateSelect_CTE
    ORDERBY CAST(DateField AS DateTime) DESC
    
    Just on a side note - since ORDER BY is the last part of the query processed (after the SELECT clause) it is aware of column alias DateField - so you can ORDER BY DateField DESC
    Last edited by RobertKaucher; 01-19-2011 at 07:44 PM.
    Reply With Quote Quote  

  4. BOBBY_TABLES RobertKaucher's Avatar
    Join Date
    Dec 2007
    Location
    Lebanon, Ohio - USA
    Posts
    4,274

    Certifications
    MCSD Web Apps/SharePoint Applications, MCITP: DBA 2005/2008, EA, EDA7, Linux+, Sec+, MCSE, MCDST, MCTS
    #3
    After thinking for a bit it occured to me you are infact looking for the most recent entry for each distinct value in column f1 and not just the most recent row. If this is the case see my blog post here:
    Elemental SQL: Using MAX(datetime) and Sub-Queries to Find the Most Recent Item

    Which would be something like this:
    Code:
    DECLARE @A TABLE(
     f1 VARCHAR(7)
    ,f2 VARCHAR(3)
    ,f3 VARCHAR(10)
    ,f4 VARCHAR(8)
    )
    INSERT @A
    SELECT 'ABC', 'ACT', '20110119', '14:00:00' UNION ALL
    SELECT 'ABC', 'INA', '20110119', '15:00:00' UNION ALL
    SELECT 'DEF', 'INA', '20110119', '15:15:00' UNION ALL
    SELECT 'DEF', 'ACT', '20110119', '14:00:00' UNION ALL
    SELECT 'DEF', 'ACT', '20110119', '15:00:00' UNION ALL
    SELECT 'GHI', 'INA', '20110119', '15:15:00' UNION ALL
    SELECT 'GHI', 'ACT', '20110119', '13:00:00'
    ;WITH dateSelect_CTE AS(
    SELECT f1
          ,f2
          ,f3+' '+f4 AS DateField
       FROM @A
    )
    SELECT * FROM dateSelect_CTE AS A
    WHERE DateField =
    (
        SELECT MAX(DateField)
        FROM dateSelect_CTE AS B
        WHERE B.f1 = A.f1
    )
    Which yields
    Code:
    f1 f2 DateField
    ABC INA 20110119 15:00:00
    DEF INA 20110119 15:15:00
    GHI INA 20110119 15:15:00
    Last edited by RobertKaucher; 01-19-2011 at 08:16 PM.
    Reply With Quote Quote  

+ Reply to Thread

Social Networking & Bookmarks