With CTE
As
(
select
[FileName],
[Trade Name],
[Invoice Number],
[Invoice Date]
,ROW_NUMBER() OVER(PARTITION BY [FileName] ORDER BY [FileName] ASC) AS FileCount
from PlacedOrderDetails
where CreatedDate >= '12/21/2012'
)
select
[FileName],
[Trade Name],
[Invoice Number],
[Invoice Date]
From CTE
Select othercol1, othercol2, otherco3, FileName, FileCount
from
(
SELECT [FileName], Max(FileCount) as [FileCount]
FROM PlacedOrderDetails
WHERE CreatedDate >= '12/21/2012'
GROUP BY [FileName]
) as a
join PlacedOrderDetails b on a.FileName = b.FileName and a.FileCount = b.FileCount
select FileName, min([Trade Name]), min([Invoice Number]), min([Invoice Date]), max(FileCount)
from PlacedOrderDetails
where CreatedDate >= '12/21/2012'
or you want to have the record with the highest FileCount for each FileName, and then the CTE would come into play.With CTE
As
(
select
[FileName],
[Trade Name],
[Invoice Number],
[Invoice Date]
,ROW_NUMBER() OVER(PARTITION BY [FileName] ORDER BY [Invoice Date] ASC) AS FileCount
,ROW_NUMBER() OVER(PARTITION BY [FileName] ORDER BY [Invoice Date] DESC) AS rn
from PlacedOrderDetails
where CreatedDate >= '12/21/2012'
)
select
[FileName],
[Trade Name],
[Invoice Number],
[Invoice Date]
From CTE
WHERE rn = 1
SELECT [FileName], count(*) as [FileCount]
FROM PlacedOrderDetails
WHERE CreatedDate >= '12/21/2012'
GROUP BY [FileName]