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 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy