April 27, 2013 at 1:53 pm
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
April 27, 2013 at 10:55 pm
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
April 29, 2013 at 9:00 am
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
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply