getting most recent row : date & time separate

dmac25dmac25 Registered Users Posts: 1 ■□□□□□□□□□
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 !

Comments

  • RobertKaucherRobertKaucher Member Posts: 4,298 ■■■■■■■■■■
    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.
    [SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]DECLARE[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] @A [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]TABLE[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]([/COLOR][/SIZE]
    [/COLOR][/SIZE][SIZE=2]f1 [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]VARCHAR[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]([/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]7[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080])[/COLOR][/SIZE]
    [SIZE=2][COLOR=#808080],[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]f2 [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]VARCHAR[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]([/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]3[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080])[/COLOR][/SIZE]
    [SIZE=2][COLOR=#808080],[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]f3 [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]VARCHAR[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]([/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]10[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080])[/COLOR][/SIZE]
    [SIZE=2][COLOR=#808080],[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]f4 [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]VARCHAR[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]([/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]8[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080])[/COLOR][/SIZE]
    [SIZE=2][COLOR=#808080])[/COLOR][/SIZE]
    [/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]INSERT[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] @A[/SIZE]
    [SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]SELECT [/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#ff0000][SIZE=2][COLOR=#ff0000]'field1'[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080],[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#ff0000][SIZE=2][COLOR=#ff0000]'f2'[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080],[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#ff0000][SIZE=2][COLOR=#ff0000]'20110119'[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080],[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#ff0000][SIZE=2][COLOR=#ff0000]'14:00:00' [/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]UNION [/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]ALL[/COLOR][/SIZE]
    [/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]SELECT [/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#ff0000][SIZE=2][COLOR=#ff0000]'field1'[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080],[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#ff0000][SIZE=2][COLOR=#ff0000]'f2'[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080],[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#ff0000][SIZE=2][COLOR=#ff0000]'20110119'[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080],[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#ff0000][SIZE=2][COLOR=#ff0000]'15:00:00' [/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]UNION [/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]ALL[/COLOR][/SIZE]
    [/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]SELECT [/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#ff0000][SIZE=2][COLOR=#ff0000]'field1'[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080],[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#ff0000][SIZE=2][COLOR=#ff0000]'f2'[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080],[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#ff0000][SIZE=2][COLOR=#ff0000]'20110119'[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080],[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#ff0000][SIZE=2][COLOR=#ff0000]'15:15:00' [/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]UNION [/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]ALL[/COLOR][/SIZE]
    [/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]SELECT [/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#ff0000][SIZE=2][COLOR=#ff0000]'field1'[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080],[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#ff0000][SIZE=2][COLOR=#ff0000]'f2'[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080],[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#ff0000][SIZE=2][COLOR=#ff0000]'20110119'[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080],[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#ff0000][SIZE=2][COLOR=#ff0000]'13:00:00'[/COLOR][/SIZE]
    [/COLOR][/SIZE]
     
    [SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080];[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]WITH[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] dateSelect_CTE [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]AS[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]([/COLOR][/SIZE]
    [/COLOR][/SIZE]
    [SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff] SELECT[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] f1[/SIZE]
    [SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]           ,[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]f2[/SIZE]
    [SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]           ,[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]f3[/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]+[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#ff0000][SIZE=2][COLOR=#ff0000]' '[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]+[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]f4 [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]AS[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] DateField[/SIZE]
    [SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]    FROM[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] @A[/SIZE]
    [SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080])[/COLOR][/SIZE]
    [/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]SELECT [/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]TOP[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] 1[/SIZE]
    [SIZE=2]   f1[/SIZE]
    [SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]  ,[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]f2[/SIZE]
    [SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]  ,[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#ff00ff][SIZE=2][COLOR=#ff00ff]CAST[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]([/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]DateField [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]AS[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]DateTime[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]) AS DateField[/COLOR][/SIZE]
    [/COLOR][/SIZE]
    [SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]FROM[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] dateSelect_CTE[/SIZE]
    [SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]ORDER[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]BY [/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#ff00ff][SIZE=2][COLOR=#ff00ff]CAST[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]([/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]DateField [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]AS [/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]DateTime[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]) [/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]DESC[/COLOR][/SIZE]
    [/COLOR][/SIZE]
    

    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
  • RobertKaucherRobertKaucher Member Posts: 4,298 ■■■■■■■■■■
    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:
    DECLARE @A TABLE(
     f1 VARCHAR(7)
    ,f2 VARCHAR(3)
    ,f3 VARCHAR(10)
    ,f4 VARCHAR([IMG]https://us.v-cdn.net/6030959/uploads/images/smilies/icon_cool.gif[/IMG]
    )
    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
    f1 f2 DateField
    ABC INA 20110119 15:00:00
    DEF INA 20110119 15:15:00
    GHI INA 20110119 15:15:00
    
Sign In or Register to comment.