Retrieving the lastest instance of a record [ SOLVED]

realJSOP

Because I was trying to do it with a single SELECT statement, which is apparently impossible. You can't have non-aggregated columns in a group by select statement (and if I'm wrong about that, I hope someone can rebut that conclusion).

.45 ACP - because shooting twice is just silly
-----
"Why don't you tie a kerosene-soaked rag around your ankles so the ants won't climb up and eat your candy ass..." - Dale Earnhardt, 1997
-----
"The staggering layers of obscenity in your statement make it a work of art on so many levels." - J. Jystad, 2001

i j russell

SELECT a.Id,
a.ItemType,
a.Title,
a.Description,
m.DateValue
FROM TableA a
LEFT JOIN
(
SELECT Id,
MAX(DateValue) as DateValue
FROM Metrics
GROUP BY Id
) m ON m.Id = a.Id

; If you only want Ids that have metrics then you can remove the LEFT to make it an inner join.

realJSOP

Actually, an ID won't be in the tables unless it has a metric. I want the data from both table a and table b, but I want only the latest data for each ID. I tried to adapt the code you posted, but It SME complains with <sarcasm> it's way to verbose <sarcasm> error message: Incorrect syntax near the keyword 'ON'. :)

.45 ACP - because shooting twice is just silly
-----
"Why don't you tie a kerosene-soaked rag around your ankles so the ants won't climb up and eat your candy ass..." - Dale Earnhardt, 1997
-----
"The staggering layers of obscenity in your statement make it a work of art on so many levels." - J. Jystad, 2001

realJSOP

I simply can't get that to work. T-SQL is being as touchy as a ex-wife seeking more child support.

.45 ACP - because shooting twice is just silly
-----
"Why don't you tie a kerosene-soaked rag around your ankles so the ants won't climb up and eat your candy ass..." - Dale Earnhardt, 1997
-----
"The staggering layers of obscenity in your statement make it a work of art on so many levels." - J. Jystad, 2001

i j russell

Try running this;

if object_id('tempdb..#TableA')is not null
DROP TABLE #TableA
go
if object_id('tempdb..#Metrics')is not null
DROP TABLE #Metrics
go

create table #TableA
(
Id int,
ItemType char(1),
Title varchar(50),
Description varchar(100)
)
go

create table #Metrics
(
ID int,
TableAId int,
datevalue datetime
)
go

insert into #TableA (ID, ItemType, Title, Description) values (1, 'A', 'First Title', 'First Description');
insert into #TableA (ID, ItemType, Title, Description) values (2, 'A', 'Second Title', 'Second Description');
insert into #TableA (ID, ItemType, Title, Description) values (3, 'B', 'Third Title', 'Third Description');
go

insert into #Metrics (ID, TableAId, datevalue) values (1, 1, '2010-01-01');
insert into #Metrics (ID, TableAId, datevalue) values (2, 1, '2010-01-02');
insert into #Metrics (ID, TableAId, datevalue) values (3, 3, '2010-01-03');
insert into #Metrics (ID, TableAId, datevalue) values (4, 2, '2010-01-04');
insert into #Metrics (ID, TableAId, datevalue) values (5, 2, '2010-01-05');
insert into #Metrics (ID, TableAId, datevalue) values (6, 3, '2010-01-06');
insert into #Metrics (ID, TableAId, datevalue) values (7, 1, '2010-01-07');
insert into #Metrics (ID, TableAId, datevalue) values (8, 2, '2010-01-08');
insert into #Metrics (ID, TableAId, datevalue) values (9, 1, '2010-01-09');
insert into #Metrics (ID, TableAId, datevalue) values (10, 3, '2010-01-10');
insert into #Metrics (ID, TableAId, datevalue) values (11, 3, '2010-01-11');
insert into #Metrics (ID, TableAId, datevalue) values (12, 1, '2010-01-12');
go

SELECT
a.ID
,a.itemType
,a.Title
,a.Description
,m.DateValue
FROM #TableA a
left join
(
select tableaid,
MAX(datevalue) as datevalue
from #Metrics
group by tableaid
) m on m.tableaid = a.id
go

drop table #Metrics
go
drop table #TableA
go

realJSOP

Hmmm... that ran just fine.

.45 ACP - because shooting twice is just silly
-----
"Why don't you tie a kerosene-soaked rag around your ankles so the ants won't climb up and eat your candy ass..." - Dale Earnhardt, 1997
-----
"The staggering layers of obscenity in your statement make it a work of art on so many levels." - J. Jystad, 2001

i j russell

Can you post the code that you have tried that gives the syntax error.

realJSOP

/****** Object: Table [dbo].[ArticleMetrics] Script Date: 03/21/2010 17:20:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ArticleMetrics](
[ArticleID] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Votes] [int] NOT NULL,
[Bookmarks] [int] NOT NULL,
[Downloads] [int] NOT NULL,
[Popularity] [decimal](5, 2) NOT NULL,
[Rating] [decimal](5, 2) NOT NULL,
[LastUpdated] [smalldatetime] NOT NULL,
[Views] [int] NOT NULL,
[DateScraped] [datetime] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[ArticleMetrics] WITH CHECK ADD CONSTRAINT [FK_ArticleMetrics_Articles] FOREIGN KEY([ArticleID])
REFERENCES [dbo].[Articles] ([ArticleID])
GO
ALTER TABLE [dbo].[ArticleMetrics] CHECK CONSTRAINT [FK_ArticleMetrics_Articles]
USE [CPAM]
GO

/****** Object: Table [dbo].[Articles] Script Date: 03/21/2010 17:20:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Articles](
[ArticleID] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[ArticleType] [int] NOT NULL,
[Title] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Description] [varchar](512) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[DatePosted] [smalldatetime] NOT NULL,
[Active] [bit] NOT NULL,
[LastScraped] [datetime] NOT NULL,
CONSTRAINT [PK_Articles] PRIMARY KEY CLUSTERED
(
[ArticleID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF

Essentially, I want to combine the appropriate rows but I only want one record returned per ArticleID that represents the LAST record that was added (determined by MAX(ArticleMetrics.DateScraped)).

.45 ACP - because shooting twice is just silly
-----
"Why don't you tie a kerosene-soaked rag around your ankles so the ants won't climb up and eat your candy ass..." - Dale Earnhardt, 1997
-----
"The staggering layers of obscenity in your statement make it a work of art on so many levels." - J. Jystad, 2001

i j russell

I think that this is what you need;

SELECT a.*,
lm.*
FROM dbo.Articles AS a
CROSS APPLY
(SELECT TOP 1 m.*
FROM dbo.ArticleMetrics AS m
WHERE m.ArticleId = a.ArticleId
ORDER BY m.DateScraped DESC) AS lm

I would also add a compound primary key to ArticleMetrics on ArticleId, DateScraped.

realJSOP

But doesn't a primary key have to be unique? If that's the case, I can't make the DateScraped column a primary key. BTW, I've never even heard of "Cross Apply" before. :)

.45 ACP - because shooting twice is just silly
-----
"Why don't you tie a kerosene-soaked rag around your ankles so the ants won't climb up and eat your candy ass..." - Dale Earnhardt, 1997
-----
"The staggering layers of obscenity in your statement make it a work of art on so many levels." - J. Jystad, 2001

i j russell

Yes PKs do have to be unique, but I was assuming that the combination of ArticleId and DateScrapped would be unique so you could include them in a compound primary key. Otherwise, you should add a identity field to uniquely identify each row. Cross Apply and Outer Apply came in with Sql Server 2005. They are great for situations like 'Show me the last 5 orders per customer' that were very difficult before with ansi sql.