CODEWITHSUNDEEP
sql-server
Funky
Funky

Reputation: 13608

SQL SERVER : select the latest comment using the max date

I have a table like so:

Id, Comment, LastUpdatedDate

I'm tyring to select the latest comment for that id. The table can have many comments on that id with different dates but I'm trying to get the latest date out of there. I've tried the following with no success:

SELECT tt.*
    FROM tagtestresultcomment tt
INNER JOIN
(
SELECT tag_id, MAX(last_update) AS MaxDateTime
FROM tagtestresultcomment
GROUP BY tag_id
) groupedtt ON tt.tag_id = groupedtt.tag_id AND tt.last_update = groupedtt.MaxDateTime
order by tag_id

Does anyone have any ideas of how to achieve this?

Thanks!

Upvotes: 2

Views: 4323

Answers (5)

Lewis Nakao
Lewis Nakao

Reputation: 7372

The "MAX" group function wasn't working for me, so I used a sub-query. I had trouble wrapping my head around your single table example, so I'm using a common parent-child 1-to-many relationship with a blog and comment tables as an example.

SELECT
  b.id,
  b.content,
  c.id,
  c.blog_id,
  c.content,
  c.last_update
FROM blog b
INNER JOIN blog_comment c
ON b.id = c.blog_id AND c.id = (
  SELECT TOP 1 id FROM blog_comment WHERE blog_id = b.id ORDER BY last_update DESC
)

The query takes a hit on my sub-query, as it will call that "SELECT TOP 1" query for each record in the blog table. I'd like to hear of a faster, more efficient example if possible.

Upvotes: 0

AhmedHuq
AhmedHuq

Reputation: 469

I have tried something like this:

declare @tagtestresultcomment table
(
id int
, comment varchar(50)
,LastUpdatedDate datetime
)

--==== Populate table

insert into @tagtestresultcomment(id,comment,LastUpdatedDate)

select 1,'My name is Arthur','2011-06-09 00:00:00' union all

select 2,'My name is DW','2011-06-19 00:00:00' union all

select 2,'Arthur is my brother','2011-06-21 00:00:00' union all

select 1,'I have a sister named DW','2011-06-21 00:00:00' union all

select 3,'I am Muffy','2011-06-14 00:00:00' union all

select 3,'I like sports','2011-06-14 00:00:00'

-- SELECT stmt

select * from @tagtestresultcomment t 
join
(
select id, MAX(lastupdateddate) as LastUpdatedDate from @tagtestresultcomment group by id
) m

on t.id = m.id 

and t.LastUpdatedDate = m.LastUpdatedDate

Upvotes: 0

Binil
Binil

Reputation: 6583

try this

Select * from tagtestresultcomment where last_update in 
(select max(last_update) from tagtestresultcomment group by tag_id)

Upvotes: 2

AdaTheDev
AdaTheDev

Reputation: 147304

It sounds like you want only the latest comment for each tag_id? In which case, here is one approach you can use from SQL 2005 and on:

;WITH CTE AS
(
SELECT *, 
    ROW_NUMBER() OVER(PARTITION BY tag_id ORDER BY last_update DESC) AS RowNo
FROM TagTestResultComment 
)

SELECT * FROM CTE WHERE RowNo = 1

Upvotes: 3

heximal
heximal

Reputation: 10517

your query code is too redundant. first

 tt.tag_id = groupedtt.tag_id AND tt.last_update = groupedtt.MaxDateTime

it's enough just 

 tt.tag_id = groupedtt.tag_id

and second, it's enough just 

SELECT [desired field list extcept last_update and ], 
  tag_id, 
  MAX(last_update) AS MaxDateTime
FROM 
  tagtestresultcomment
group by 
 tag_id, [desired field list extcept last_update and tag_id]

at all to achieve your objective

Upvotes: 1

Related Questions