Select newest date from table

[anorthcote]

Hi,

I have a table as follows

ID Month Year
13 Mar 2006
17 Apr 2004
18 Sept 2004
19 Mar 2007
19 Sept 2004
20 Dec 2004
21 Nov 2007
23 Mar 2004
24 Mar 2005
25 July 2005
25 Oct 1998
26 May 2006
28 Apr 1999
28 Sept 2005
29 Apr 2006
29 Feb 1999
30 June 2007


I want to select from this just the newest date entry for each ID. I also want to then convert the Month and Year columns in to a format that VB can interpret as a date.

I'm a relative beginner to SQL query and I am struggling to work this one out. I hope someone can help?

 

[bborissov]

DECLARE @Test TABLE (Id int, Mnt varchar(10), Yr int)
INSERT INTO @Test VALUES (13,'Mar',2006)
INSERT INTO @Test VALUES (17,'Apr',2004)
INSERT INTO @Test VALUES (18,'Sept',2004)
INSERT INTO @Test VALUES (19,'Mar',2007)
INSERT INTO @Test VALUES (19,'Sept',2004)
INSERT INTO @Test VALUES (20,'Dec',2004)
INSERT INTO @Test VALUES (21,'Nov',2007)
INSERT INTO @Test VALUES (23,'Mar',2004)
INSERT INTO @Test VALUES (24,'Mar',2005)
INSERT INTO @Test VALUES (25,'July',2005)
INSERT INTO @Test VALUES (25,'Oct',1998)
INSERT INTO @Test VALUES (26,'May',2006)
INSERT INTO @Test VALUES (28,'Apr',1999)
INSERT INTO @Test VALUES (28,'Sept',2005)
INSERT INTO @Test VALUES (29,'Apr',2006)
INSERT INTO @Test VALUES (29,'Feb',1999)
INSERT INTO @Test VALUES (30,'June',2007)

SELECT Id,
       MAX(CAST('01-'+LEFT(Mnt,3)+'-'+STR(Yr,4) as datetime)) AS MaxDate
FROM @Test
GROUP BY Id

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP

 

[vongrunt]

That should do it, assuming server language is set to US/UK English:

select ID, max(convert(datetime, left([Month], 3)+' 01, '+ convert(varchar(4), [Year]), 107)) as newestDate
from yourtable
group by ID

Newest dates per ID are represented as datetime values containing first day of month.




------
chemistry - the only natural science that can be broken down into the categories a) making drugs and b) blowing stuff up

 

[anorthcote]

That's great... thank you both.