getting most recent row : date & time separate
dmac25
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 !
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
-
RobertKaucher
Member Posts: 4,299 ■■■■■■■■■■
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 -
RobertKaucher
Member Posts: 4,299 ■■■■■■■■■■
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 yieldsf1 f2 DateField ABC INA 20110119 15:00:00 DEF INA 20110119 15:15:00 GHI INA 20110119 15:15:00