0

I have a table A that I need to join to table B. Table B holds important info including CreatedDate. I want to only return results for the most recent created date. My SQL below appears correct but it is returning multiple results since column Y has multiple distinct values for each row.

(X = Foreign Key, Y = Attribute that changes with each row)

SELECT *
FROM TABLE_A T
    INNER JOIN 
     (SELECT X, Y, MAX(CreatedDate) AS Created
      FROM TABLE_B
      GROUP BY X, Y) SUB ON SUB.X = T.X

How do I prevent duplicated rows and only show the max created date row?

Thanks!

John Doe
  • 139
  • 1
  • 1
  • 10
  • Why are you grouping on Y if you if you don't want multiple rows? Is it necessary? Do you really want SELECT TOP 1..... ORDER BY CreateDate DESC? – SteveB Nov 02 '17 at 14:45
  • some sample data might be useful to highlight your problem. sounds like you need to may need to exclude `Y` from the subquery and maybe look at using `TOP 1` with an `ORDER BY`, although that's off the top of my head without testing. – Tanner Nov 02 '17 at 14:46
  • @SteveB because if I don't, I get the error message "Y is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause." Will select TOP 1 just return 1 result for the whole join?... actually I can check that... – John Doe Nov 02 '17 at 14:49
  • @SteveB Select TOP 1 worked. I thought it would only return 1 result for the whole sub query. Thanks! – John Doe Nov 02 '17 at 14:52
  • @Tanner yep, that worked. I thought using TOP 1 would only return one result for the whole sub query.Thanks! – John Doe Nov 02 '17 at 14:54

0 Answers0