SQL Server - Distinct values in SQL code and MAX date values

Hi all,

This is my current script

It returns results as expected however I only want the script to return the DISTINCT STORE NO, and the latest date next to that store. NO stores should duplicate....

So at the moment the results are like the follwoing. You can see there are multiple results per store for example below.

store	path	status	date	file	transactions
3	D:\Progra~1\NSB\aw_xpr_01\NSBPollData\AWL.01281518.TR	Completed	27:52.3	XPOLLD00035983.TR	15
5	D:\Progra~1\NSB\aw_xpr_01\NSBPollData\AWL.01281518.TR	Completed	27:52.3	XPOLLD00053616.TR	13
9	D:\Progra~1\NSB\aw_xpr_01\NSBPollData\AWL.01281518.TR	Completed	27:52.3	XPOLLD00094458.TR	15
10	D:\Progra~1\NSB\aw_xpr_01\NSBPollData\AWL.01281518.TR	Completed	27:52.3	XPOLLD00106003.TR	31
11	D:\Progra~1\NSB\aw_xpr_01\NSBPollData\AWL.01281518.TR	Completed	27:52.3	XPOLLD00119212.TR	21
14	D:\Progra~1\NSB\aw_xpr_01\NSBPollData\AWL.01281518.TR	Completed	27:52.3	XPOLLD00144551.TR	25
19	D:\Progra~1\NSB\aw_xpr_01\NSBPollData\AWL.01281518.TR	Completed	27:52.3	XPOLLD00194934.TR	6
21	D:\Progra~1\NSB\aw_xpr_01\NSBPollData\AWL.01281518.TR	Completed	27:52.3	XPOLLD00215818.TR	17
23	D:\Progra~1\NSB\aw_xpr_01\NSBPollData\AWL.01281518.TR	Completed	27:52.3	XPOLLD00236156.TR	10
27	D:\Progra~1\NSB\aw_xpr_01\NSBPollData\AWL.01281518.TR	Completed	27:52.3	XPOLLD00274903.TR	11
32	D:\Progra~1\NSB\aw_xpr_01\NSBPollData\AWL.01281518.TR	Completed	27:52.3	XPOLLD00326093.TR	21
34	D:\Progra~1\NSB\aw_xpr_01\NSBPollData\AWL.01281518.TR	Completed	27:52.3	XPOLLD00346195.TR	22
38	D:\Progra~1\NSB\aw_xpr_01\NSBPollData\AWL.01281518.TR	Completed	27:52.3	XPOLLD00380335.TR	27
39	D:\Progra~1\NSB\aw_xpr_01\NSBPollData\AWL.01281518.TR	Completed	27:52.3	XPOLLD00397283.TR	17
9	D:\Progra~1\NSB\aw_xpr_01\NSBPollData\AWL.01281518.TR	Completed	27:52.3	XPOLLD00403378.TR	15
41	D:\Progra~1\NSB\aw_xpr_01\NSBPollData\AWL.01281518.TR	Completed	27:52.3	XPOLLD00412137.TR	11
43	D:\Progra~1\NSB\aw_xpr_01\NSBPollData\AWL.01281518.TR	Completed	27:52.3	XPOLLD00435100.TR	9
11	D:\Progra~1\NSB\aw_xpr_01\NSBPollData\AWL.01281518.TR	Completed	27:52.3	XPOLLD00449716.TR	26
45	D:\Progra~1\NSB\aw_xpr_01\NSBPollData\AWL.01281518.TR	Completed	27:52.3	XPOLLD00451232.TR	37
46	D:\Progra~1\NSB\aw_xpr_01\NSBPollData\AWL.01281518.TR	Completed	27:52.3	XPOLLD00462542.TR	17
19	D:\Progra~1\NSB\aw_xpr_01\NSBPollData\AWL.01281518.TR	Completed	27:52.3	XPOLLD00482285.TR	16

I would like the results to show for example

store	path	status	date	file	transactions
3	D:\Progra~1\NSB\aw_xpr_01\NSBPollData\AWL.01281518.TR	Completed	27:52.3	XPOLLD00035983.TR	15
5	D:\Progra~1\NSB\aw_xpr_01\NSBPollData\AWL.01281518.TR	Completed	27:52.3	XPOLLD00053616.TR	13
9	D:\Progra~1\NSB\aw_xpr_01\NSBPollData\AWL.01281518.TR	Completed	27:52.3	XPOLLD00094458.TR	15
10	D:\Progra~1\NSB\aw_xpr_01\NSBPollData\AWL.01281518.TR	Completed	27:52.3	XPOLLD00106003.TR	31
11	D:\Progra~1\NSB\aw_xpr_01\NSBPollData\AWL.01281518.TR	Completed	27:52.3	XPOLLD00119212.TR	21
14	D:\Progra~1\NSB\aw_xpr_01\NSBPollData\AWL.01281518.TR	Completed	27:52.3	XPOLLD00144551.TR	25
19	D:\Progra~1\NSB\aw_xpr_01\NSBPollData\AWL.01281518.TR	Completed	27:52.3	XPOLLD00194934.TR	6
21	D:\Progra~1\NSB\aw_xpr_01\NSBPollData\AWL.01281518.TR	Completed	27:52.3	XPOLLD00215818.TR	17
23	D:\Progra~1\NSB\aw_xpr_01\NSBPollData\AWL.01281518.TR	Completed	27:52.3	XPOLLD00236156.TR	10
27	D:\Progra~1\NSB\aw_xpr_01\NSBPollData\AWL.01281518.TR	Completed	27:52.3	XPOLLD00274903.TR	11
32	D:\Progra~1\NSB\aw_xpr_01\NSBPollData\AWL.01281518.TR	Completed	27:52.3	XPOLLD00326093.TR	21
34	D:\Progra~1\NSB\aw_xpr_01\NSBPollData\AWL.01281518.TR	Completed	27:52.3	XPOLLD00346195.TR	22
38	D:\Progra~1\NSB\aw_xpr_01\NSBPollData\AWL.01281518.TR	Completed	27:52.3	XPOLLD00380335.TR	27
39	D:\Progra~1\NSB\aw_xpr_01\NSBPollData\AWL.01281518.TR	Completed	27:52.3	XPOLLD00397283.TR	17

***I want to be able to only show the latest file processed (the date) and the distinct store. 

THE SCRIPT I  AM USING

drop table #TricklePOLL
select Substring ("Filename",9,2) as Store_no,tp.[filename], tp.[id]
into #TricklePOLL
from [fn_xpr_01].[dbo].[Tr_PollFileHistory] tp
SELECT 
tpr.[store_no],
 ,td.[path]
      ,case td.[done_file_type]
when '1' then 'Completed'
when '0' then 'In-Progress'
end as 'Status of Processed File',
  td.[done_file_date_time] as 'File Processed at',
      tp.[filename]
      ,tp.[transactions]
  FROM [fn_xpr_01].[dbo].[Tr_PollFileHistory] tp
inner join [fn_xpr_01].[dbo].[Tr_TranslateType] tt on tp.[translate_type] = [tt].[translate_type]
inner join [fn_xpr_01].[dbo].[Tr_Directory] td on tp.[dir_id] = td.[id]
inner join #TricklePOLL tpr on tp.[id] = tpr.[id]
order by td.[done_file_date_time] desc
IS THIS POSSIBLE<

Have you tried using the DISTINCT Keyword for your query:
SELECT  DISTINCT
tpr.[store_no],
,td.[path]
,case td.[done_file_type]
when '1' then 'Completed'
when '0' then 'In-Progress'
end as 'Status of Processed File',
td.[done_file_date_time] as 'File Processed at',
tp.[filename]
,tp.[transactions]
FROM [fn_xpr_01].[dbo].[Tr_PollFileHistory] tp
inner join [fn_xpr_01].[dbo].[Tr_TranslateType] tt on tp.[translate_type] = [tt].[translate_type]
inner join [fn_xpr_01].[dbo].[Tr_Directory] td on tp.[dir_id] = td.[id]
inner join #TricklePOLL tpr on tp.[id] = tpr.[id]
order by td.[done_file_date_time] desc

Hello,

Try as following way

SELECT tt.*

FROM TableName tt

INNER JOIN

    (

    SELECT GroupByFieldName, MAX(datetime) AS MaxDateTime

    FROM TableName

    GROUP BY GroupByFieldName

    ) groupedtt ON tt.GroupByFieldName = groupedtt.GroupByFieldName AND tt.datetime = groupedtt.MaxDateTime

Hope this is helpful !

Thanks

 

 

 

 

 

Hello,

Change your query as following way

SELECT

Substring (tp.[filename],9,2) as store_no,

 ,td.[path]

    ,case td.[done_file_type]

when '1' then 'Completed'

when '0' then 'In-Progress'

end as 'Status of Processed File',

  td.[done_file_date_time] as 'File Processed at',

    tp.[filename]

    ,tp.[transactions]

  FROM [fn_xpr_01].[dbo].[Tr_PollFileHistory] tp

inner join [fn_xpr_01].[dbo].[Tr_TranslateType] tt on tp.[translate_type] = [tt].[translate_type]

inner join [fn_xpr_01].[dbo].[Tr_Directory] td on tp.[dir_id] = td.[id]

INNER JOIN

    (

    SELECT Substring (innertp.[filename],9,2) as storenumber, MAX(innertp.[done_file_date_time]) AS MaxDateTime

    FROM [fn_xpr_01].[dbo].[Tr_PollFileHistory] as innertp

    GROUP BY Substring (innertp.[filename],9,2)

    ) groupedtt ON (tp.[filename],9,2) = groupedtt.storenumber AND td.[done_file_date_time] = groupedtt.MaxDateTime

 

Hope this is helpful !

Thanks

 

 

 

 

 

Have you tried using the DISTINCT Keyword for your query:
SELECT  DISTINCT
tpr.[store_no],
,td.[path]
,case td.[done_file_type]
when '1' then 'Completed'
when '0' then 'In-Progress'
end as 'Status of Processed File',
td.[done_file_date_time] as 'File Processed at',
tp.[filename]
,tp.[transactions]
FROM [fn_xpr_01].[dbo].[Tr_PollFileHistory] tp
inner join [fn_xpr_01].[dbo].[Tr_TranslateType] tt on tp.[translate_type] = [tt].[translate_type]
inner join [fn_xpr_01].[dbo].[Tr_Directory] td on tp.[dir_id] = td.[id]
inner join #TricklePOLL tpr on tp.[id] = tpr.[id]
order by td.[done_file_date_time] desc