Hi all,
I'm trying to get the latest date for every distinct value in a column:
CREATE TABLE [dbo].[Ledger](
[ID] [int] NOT NULL,
[ProfileId] [int] NULL,
[Date] [datetime] NULL,
[Time] [datetime] NULL) ON [PRIMARY]
GO
--Sample Data
--Select Top 8 ProfileId, Date, Paid
--From Ledger As L
1657 Nov 10 2009 12:00AM 150.00
1657 May 8 2009 12:00AM 150.00
1773 Jun 7 2010 12:00AM 125.00
1773 Apr 19 2006 12:00AM 99.00
1817 Jun 2 2008 12:00AM 109.00
1825 Feb 14 2006 12:00AM 99.00
1828 Dec 3 2008 12:00AM 135.00
1828 Jul 15 2009 12:00AM 135.00
--Desired output (Only those rows with the latest date):
1657 Nov 10 2009 12:00AM 150.00
1773 Jun 7 2010 12:00AM 125.00
1817 Jun 2 2008 12:00AM 109.00
1825 Feb 14 2006 12:00AM 99.00
1828 Jul 15 2009 12:00AM 135.00
I tried Select ProfileID, Max(Date), Paid, even added a Distinct to the ProfileID, but keep getting duplicate ProfileID's.
TIA
Try this:
WITH Base AS (
SELECT
[l].[ProfileId]
, [l].[Date]
, [l].[Paid]
, [rn] = ROW_NUMBER() OVER (PARTITION BY [l].[ProfileId] ORDER BY [l].[Date] DESC)
FROM
[dbo].[Ledger] AS [l]
)
SELECT
.[ProfileId]
, .[Date]
, .[Paid]
FROM
[base] AS
WHERE
.[rn] = 1;