2

I have articles table, article_comments.

I want to get the value: last_modified_all of article. I need this value to be the great date of this two:

  1. the last_modified field of articles table.
  2. the last comment of article_comments last_modified column.

Can someone help me? the structure is very simple, and you can guess is without problem.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
stacker
  • 14,641
  • 17
  • 46
  • 74

1 Answers1

1

This is the greatest-n-per-group problem that comes up frequently on Stack Overflow. Here's how I solve it:

SELECT a.article_id, a.last_modified, c1.last_modified AS last_comment_date
FROM articles AS a
JOIN article_comments AS c1
 ON (a.article_id = c2.article_id)
LEFT OUTER JOIN article_comments AS c2
 ON (a.article_id = c2.article_id AND c1.last_modified < c2.last_modified)
WHERE c2.article_id IS NULL;

This has a chance of producing more than one row per article unless article_comments.last_modified is unique. To resolve this, use a tiebreaker. For instance, if there's an auto-incrementing primary key column:

SELECT a.article_id, a.last_modified, c1.last_modified AS last_comment_date
FROM articles AS a
JOIN article_comments AS c1
 ON (a.article_id = c2.article_id)
LEFT OUTER JOIN article_comments AS c2
 ON (a.article_id = c2.article_id AND (c1.last_modified < c2.last_modified
     OR c1.last_modified = c2.last_modified AND c1.comment_id < c2.comment_id))
WHERE c2.article_id IS NULL;
Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • Thanks. I solved it with `OUTER APPLY`. How can I get the latest date of two dates in sql server query? – stacker May 26 '10 at 22:40
  • +1, but what is the N in "greatest-n-per-group"? I thought the problem was called "select records holding groupwise maximum" :) – Andomar May 26 '10 at 22:42
  • I want to show only the `LastDateOf(a.last_modified, c1.last_modified AS last_comment_date) as last_modified_all` – stacker May 26 '10 at 22:44
  • This was helped http://stackoverflow.com/questions/63447/how-do-you-perform-an-if-then-in-a-sql-select – stacker May 26 '10 at 22:49
  • @Andomar: In this case, N is 1. In some variations on the question, people may ask for most recent three per article, or something else. And of course *least* can sometimes be substituted for *greatest*. But the tag that has been coined on Stack Overflow is `greatest-n-per-group` which is close enough. – Bill Karwin May 26 '10 at 23:29
  • `article_comments AS c1 ON ( a.article_id = c2.article_id )` looks like it should be `article_comments AS c1 ON ( a.article_id = c1.article_id )` – Timo Huovinen Mar 26 '12 at 10:50
  • @YuriKolovsky, you are correct. Feel free to edit it. I've stopped posting to StackOverflow (except to respond to comments). – Bill Karwin Mar 26 '12 at 16:10