I am going to assume that in the real table there is some data that would preclude you simply doing this.

select a,b, max(date), max(c), max(d) from tbl group by a,b

I can see two possible solutions other then what Morgan and Ben suggested.

The ugliest is to use a loop and scan through the records one by one. Create an index that contains all the fields you want to include in the selection critera and then use that to pick through the records one by one, inserting the records you want into a second table.

I do not think you need to go that far here, but I have ended up using them when the critera for selecting records would not condense into a single query or when the critera depended on looking at more then one record at a time.

The second goes like this. Run a query to group by (a,b) and stick them in the second table, with data filled in with nulls. Then do a series of queries like this to fill in the data.

\nUpdate tbl2 set (all the data fields) from\n  (select B.* from \n    (select a,b, max(date) as xDate, count(1) as RowCount, c,d\n      from tbl group by a,b,c,d) as A,\n  tbl as B where B.date = a.xDate and RowCount = 1) as GD,\ntbl2 where GD.a = tbl2.a and GD.b = tbl2.b and tbl2.date is null\n

The idea in this case is that query picks out all the rows where there is only one date and moves them over. Then you can write another query that looks at the next field and only moves them over and so on.

I've used this a couple of times when 90% or more of the records where clean and there where only a handful of records that needed scrubbing, often by hand.

Given what you have said, in SLQ Sever I would probably end up using the same solution you came up with, but would put a primary key on the table and create an index that covers the fields that are part of the critera.

Jay