Reporting problem

[BobBob10]

Hi there I have the following dataset,

ref to_date ref1
35447 2006-03-24 00:00:00.000 -1
54001 2006-03-24 00:00:00.000 -6
54001 2007-03-24 00:00:00.000 -8
54201 2006-12-31 00:00:00.000 -990

From this record set I want 3 rows, first, third and fourth. I don't need the second due to the refernce number being the same as the third, but the third line the date is greater than the second.

Does anyone know the SQL for this, I need to keep all three columns.

Cheers

Bob

 

[Bobbber]

SELECT ref, to_date, ref1 FROM tbl
inner join

(SELECT ref as X, MAX(to_date) as y
FROM tbl
GROUP BY X) AS tbltbl
ON tbltbl.X=tbl.ref and tbltbl.Y=tbl.to_date

 

[SQLDenis]

Change table1 to your table name

select t1.* from table1 t1
join (select max(to_date) as Maxdate,ref
from table1 group by ref) t2 on t1.ref = t2.ref and t1.to_date = t2.Maxdate

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[Bobbber]

'tbl' is the table name of the dataset you supplied.