This SQL Table:
CREATE TABLE [dbo].[replication2](
[id] [smallint] IDENTITY(1,1) NOT NULL,
[VMName] [varchar](50) NULL,
[PrimaryHost] [varchar](50) NULL,
[ReplicaHost] [varchar](50) NULL,
[LastReplicaDate] [varchar](50) NULL,
[LastReplicaDateTime] [datetime2](7) NULL,
CONSTRAINT [PK_replication2] PRIMARY KEY CLUSTERED
This table contains thousands of rows, each row representing a single instance of VM replication such as:
id VMName PrimaryHost ReplicaHost LastReplicaDate LastReplicaDateTime
1608 vm1 hostA hostB 6/13/2015 8:44:37 am 2015-06-13 08:44:37.0000000
I would like to select all [VMName, PrimaryHost, ReplicaHost] such that for each unique [VMName, PrimaryHost, ReplicaHost] I only get the latest row, based on LastReplicaDateTime.
I've tried different group-by having methods but can't come up with anything that works.