CREATE TABLE [dbo].[#Temp] (
	[TempID] BIGINT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
	[Account] INT NOT NULL,
	[DepotId] SMALLINT NOT NULL,
	[Insert_Time] DATETIME NOT NULL,
	[FullName] NVARCHAR(50) NULL)
	ON [PRIMARY];

INSERT INTO #Temp VALUES (123, 100, N'2010-07-27 09:50:00', N'Name 1');
INSERT INTO #Temp VALUES (123, 100, N'2010-07-27 10:50:00', N'Name 2');
INSERT INTO #Temp VALUES (123, 200, N'2010-07-27 10:50:00', N'FooBar');

INSERT INTO #Temp VALUES (456, 100, N'2010-07-27 09:50:00', N'Another Name 1');
INSERT INTO #Temp VALUES (456, 100, N'2010-07-27 09:50:00', N'Another Name 2');
INSERT INTO #Temp VALUES (456, 300, N'2010-07-27 11:50:00', N'San Diego');


SELECT * FROM #Temp;

-- This is the result I need the query to produce:

-- 2	123	100	2010-07-27 10:50:00.000	Name 2
-- 3	123	200	2010-07-27 10:50:00.000	FooBar
-- 5	456	100	2010-07-27 09:50:00.000	Another Name 2
-- 6	456	300	2010-07-27 11:50:00.000	San Diego

DROP TABLE #Temp

SELECT TempID, Account, DepotId, Insert_Time, FullName
  FROM (SELECT TempID, Account, DepotId, Insert_Time, FullName,
               ROW_NUMBER() OVER(PARTITION BY Account, DepotId ORDER BY TempID DESC) AS row_num
          FROM #Temp) as t
 WHERE row_num = 1;

quote:

---

Originally posted by Bustaz Kool

At long last WE get to help YOU! Woo and Hoo!

---

select
t.*
from
(
select
max_tempID
from
(
select account
	,depotID
    ,count(*) as nof
	,max(tempID) as max_tempID
from #temp
group by account, depotID
having count(*) > 1
) as x
union all
select
max_tempID
from
(
select account
	,depotID
    ,count(*) as nof
	,max(tempID) as max_tempID
from #temp

group by account, depotID
having count(*) = 1
) as x
) as y
join #temp as t
on t.tempID = y.max_tempId

select 
* 
from 
(select  *,row_number() over (partition by account, depotID order by tempID desc) as r_n from #temp
) as x
from #temp
where r_n = 1