Posted by Erland Sommarskog on 07/09/05 01:13

(throat.wobbler.mangrove@gmail.com) writes:
> One final thing...I have tried GROUPING BY all of the fields in s (in
> the wider expression), but if I have a record with identical
> RCRD_REFNOs and max MODIF_DTTM's, but one of the other fields differs,
> then I get two records for that unique ID...which brings me back once
> again to the TOP 1 thing...I'm now stuck!

There is a bit of lack examples that illustrates what you are up to,
but if I add your three posts together, my guess is that you have a
staging table that may contain junk.

Can't you can an IDENTITY column to the table? Without a unique key
for the table, it's going to be difficult to pick 1 out of 2 duplicates?

In such case you would do:

SELECT t.RCRD_REFNO, maxid = MAX(id)
FROM tbl t
JOIN (SELECT RCRC_RECNNO, MODIF_DTTM = MAX(MODIF_DTTM)
FROM tbl
GROUP BY RCRD_REFNO) AS u ON t.RCRD_REFNO = u.RCRD_REFNO
AND t.MODIF_DTTM = u.MODIF_DTTM
GROUP BY t.RCRD_RECNO


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Back to original message]