get max

Question

Post reply

get max

sstacy7

SSC-Addicted

Points: 441
More actions
April 27, 2013 at 1:53 pm

#273371

CREATE TABLE [dbo].[Table_A](
[id] [smallint] NULL,
[name] [varchar](50) NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Table_B](
[ID] [smallint] NULL,
[DATE] [date] NULL,
[ORDER] [varchar](50) NULL
) ON [PRIMARY]
GO
INSERT INTO [Test].[dbo].[Table_A] ([id] ,[name]) VALUES (1,'Name1')
GO
INSERT INTO [Test].[dbo].[Table_A] ([id] ,[name]) VALUES (2,'Name2')
GO
INSERT INTO [Test].[dbo].[Table_A] ([id] ,[name]) VALUES (3,'Name3')
GO
INSERT INTO [Test].[dbo].[Table_A] ([id] ,[name]) VALUES (4,'Name4')
GO
INSERT INTO [Test].[dbo].[Table_A] ([id] ,[name]) VALUES (5,'Name5')
GO
INSERT INTO [Test].[dbo].[Table_A] ([id] ,[name]) VALUES (6,'Name6')
GO
INSERT INTO [Test].[dbo].[Table_A] ([id] ,[name]) VALUES (7,'Name7')
GO
INSERT INTO [Test].[dbo].[Table_A] ([id] ,[name]) VALUES (8,'Name8')
GO
INSERT INTO [Test].[dbo].[Table_A] ([id] ,[name]) VALUES (9,'Name9')
GO
INSERT INTO [Test].[dbo].[Table_A] ([id] ,[name]) VALUES (10,'Name10')
GO
INSERT INTO [Test].[dbo].[Table_B] ([ID],[DATE],[ORDER])VALUES (1,'2013-1-2','a')
GO
INSERT INTO [Test].[dbo].[Table_B] ([ID],[DATE],[ORDER])VALUES (2,'2013-1-2','a')
GO
INSERT INTO [Test].[dbo].[Table_B] ([ID],[DATE],[ORDER])VALUES (3,'2013-1-2','a')
GO
INSERT INTO [Test].[dbo].[Table_B] ([ID],[DATE],[ORDER])VALUES (4,'2013-1-2','a')
GO
INSERT INTO [Test].[dbo].[Table_B] ([ID],[DATE],[ORDER])VALUES (5,'2013-1-2','a')
GO
INSERT INTO [Test].[dbo].[Table_B] ([ID],[DATE],[ORDER])VALUES (1,'2013-1-20','b')
GO
INSERT INTO [Test].[dbo].[Table_B] ([ID],[DATE],[ORDER])VALUES (2,'2013-1-20','b')
GO
INSERT INTO [Test].[dbo].[Table_B] ([ID],[DATE],[ORDER])VALUES (3,'2013-1-20','b')
GO
INSERT INTO [Test].[dbo].[Table_B] ([ID],[DATE],[ORDER])VALUES (4,'2013-1-20','b')
GO
INSERT INTO [Test].[dbo].[Table_B] ([ID],[DATE],[ORDER])VALUES (5,'2013-1-20','b')
GO
INSERT INTO [Test].[dbo].[Table_B] ([ID],[DATE],[ORDER])VALUES (1,'2013-2-20','c')
GO
INSERT INTO [Test].[dbo].[Table_B] ([ID],[DATE],[ORDER])VALUES (2,'2013-2-20','c')
GO
INSERT INTO [Test].[dbo].[Table_B] ([ID],[DATE],[ORDER])VALUES (3,'2013-2-20','c')
GO
How do I do something like this?
select a.name, b.[DATE], b.[ORDER]
from dbo.Table_A a
left outer join dbo.Table_B b on a.id = b.ID
and b.[DATE] = (select MAX([date]) from dbo.Table_B b inner join dbo.Table_A a on b.ID = a.id)
This is the result I need.
Name12/20/2013c
Name22/20/2013c
Name32/20/2013c
Name41/20/2013b
Name51/20/2013b
Name6NULL NULL
Name7NULL NULL
Name8NULL NULL
Name9NULL NULL
Name10NULL NULL

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

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

Calibear Ten Centuries Points: 1377 More actions · Answer 1

You just needed to change up your on statement just a bit:

select a.name, b.[DATE], b.[ORDER]

from dbo.Table_A a

left outer join dbo.Table_B b on a.id = b.ID

and b.[DATE] = (select MAX([DATE]) from dbo.Table_B[highlight=#ffff11] c where c.ID = b.ID[/highlight])

--------
For SQL Tips, check out my blog: http://lantztechknowledge.blogspot.com/[/url]
You can also follow my twitter account to get daily updates: @BLantz2455

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 2

One way of doing this..

SELECTa.name, b.[DATE], b.[ORDER]

FROMdbo.Table_A a

LEFT OUTER JOIN(

SELECTROW_NUMBER() OVER( PARTITION BY b.ID ORDER BY b.[DATE] DESC ) AS RN, *

FROMdbo.Table_B AS b

) AS b ON a.id = b.ID AND b.RN = 1

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/