Retrieving the lastest instance of a record [ SOLVED]

[realJSOP]

Here's my situation: 0) There are a number of records for a given "id", and there is more than one "id" in the table. So, I could have 10 records for id1, and 5 records for id2. 1) These records contain a datetime column on which I want to base by query. 2) I will be retrieving data from two tables. 3) I tried a simple query first (which worked fine):

select id, max(dateColumn) from tabelname group by id

The problem is that I want additional columns from each table that shouldn't be aggregated, but sql server says I need aggregate functions for all of the other columns. Do I have to run a query and put the results into a temp table, and then run a second query against the actual data to retrieve the records I want, or is there a magic generic aggregate function I can use on the other fields? I am of the current opinion that SQL sucks. :)

.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

[Luc Pattyn]

Wow. Your question well exceeds what I know about SQL. I've never used temp tables, I very rarely use nested selects. I'm still learning, mostly by reading the SQL forum. Did you try the TOP 1 ... ORDER BY ... approach? that one typically works for me.

John Simmons / outlaw programmer wrote:

I am of the current opinion that SQL sucks.

I'm afraid that will never change. At best, one gets used to it. Suggestion: post your exact SQL statement, and wait for Mycroft to comment on it. He's probably the most knowledgeable around here. :)

Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles]

---

I only read code that is properly formatted, adding PRE tags is the easiest way to obtain that.

---

[Luc Pattyn]

Now how is the SQL code shown as the solution different from the one shown in your last post above? I'm referring to:

select id, max(dateColumn) from tabelname group by id

:)

Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles]

---

I only read code that is properly formatted, adding PRE tags is the easiest way to obtain that.

---

[realJSOP]

I had to do something like this: create temp table select into temp table using group by select from permanent AD temp table for desired result Here's the whole this

if object_id('tempdb..#DUAL')is not null
DROP TABLE #DUAL

create table #DUAL
(
ID int,
datevalue datetime
)

insert into #DUAL (ID, datevalue)
select id, max(datevalue)
from metrics
group by ID

SELECT
a.ID
,a.itemType
,a.Title
,a.Description
,m.DateValue
FROM tableA a, metrics m, #Dual d
WHERE a.id = d.id AND a.ID = m.ID AND m.datevalue = d.datevalue

drop table #DUAL

.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]

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.