Remove duplicates (1 to many) or write a subquery that solves my problem

Referring to the diagram below the records table has unique Records. Each record is updated, via comments through an Update Table. When I join the two I get lots of duplicates.

1. How to remove duplicates? Group By does not work for me as I have more than 10 fields in select query and some of them are functions.

2. Write a sub query which pulls the last updates in the Update开发者_高级运维 table for each record that is updated in a particular month. Joining with this sub query will solve my problem.

Thanks!

Edit Table structure that is of interest is

create table Records(
recordID int,
90more_fields various
)

create table Updates(
update_id int,
record_id int,
comment text,
byUser varchar(25),
datecreate datetime
)

---

Here's one way.

SELECT * /*But list columns explicitly*/
FROM   Orange o
       CROSS APPLY (SELECT TOP 1 *
                    FROM   Blue b
                    WHERE  b.datecreate >= '20110901'
                           AND b.datecreate < '20111001'
                           AND o.RecordID = b.Record_ID2
                    ORDER  BY b.datecreate  DESC) b 

---

Based on the limited information available...

WITH cteLastUpdate AS (
    SELECT Record_ID2, UpdateDateTime, 
           ROW_NUMBER() OVER(PARTITION BY Record_ID2 ORDER BY UpdateDateTime DESC) AS RowNUM
        FROM BlueTable
        /* Add WHERE clause if needed to restrict date range */
)
SELECT *
    FROM cteLastUpdate lu
        INNER JOIN OrangeTable o
            ON lu.Record_ID2 = o.RecordID
    WHERE lu.RowNum = 1

---

Last updates per record and month:

SELECT *
  FROM UPDATES outerUpd
 WHERE exists
 (
      -- Magic part
      SELECT 1
        FROM UPDATES innerUpd
       WHERE innerUpd.RecordId = outerUpd.RecordId
    GROUP BY RecordId
           , date_part('year', innerUpd.datecolumn)
           , date_part('month', innerUpd.datecolumn)
      HAVING max(innerUpd.datecolumn) = outerUpd.datecolumn
 )

(Works on PostgreSQL, date_part is different in other RDBMS)