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

Asked By jason barry on 27-Jan-12 11:41 PM
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<
Venkat K replied to jason barry on 28-Jan-12 12:33 AM
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

Chintan Vaghela replied to jason barry on 28-Jan-12 12:41 AM

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

 

 

 

 

 

Chintan Vaghela replied to jason barry on 28-Jan-12 01:00 AM

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

 

 

 

 

 

Venkat K replied to jason barry on 28-Jan-12 02:13 AM
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