Skip to main content
Database Administrators

Return to Answer

Redo the answer
Source Link
jyao
jyao
  • 3.1k
  • 1
  • 13
  • 27

I think the following query is more intuitive to understand:

selectcreate maxtable SLVs_Flagged (PARAMETER_NAME varchar(30), GW_LOCATION_ID varchar(30), Report_Result int, DETECT_FLAG char(1) as)
go
insert ReportResultinto dbo.SLVs_Flagged 
values ('abc', PARAMETER_NAME'cor1', GW_LOCATION_ID128, 'N'), ('abc', 'cor1', 12, 'Y')
t.DETECT_FLAG, --('def', all'cor1', other500, columns'Y'), can('def', be'cor1', listed50, here'N')
go

;with s as (
select max(Report_Result) as Report_Result, PARAMETER_NAME,  GW_LOCATION_ID
from SLVs_Flagged s
cross applygroup (by PARAMETER_NAME, GW_LOCATION_ID)
SELECT *s.Report_Result, s.PARAMETER_NAME,  s.GW_LOCATION_ID, t.DETECT_FLAG
FROM SLVs_Flagged t
whereinner join s 
on t.PARAMETER_NAME = s.PARAMETER_NAME 
 and t.GW_LOCATION_ID = s.GW_LOCATION_ID 
 and t.Report_Result = s.ReportResult 
) T
group by s.PARAMETER_NAME, s.GW_LOCATION_IDReport_Result

I think the following query is more intuitive to understand

select max(Report_Result) as ReportResult, PARAMETER_NAME, GW_LOCATION_ID,
t.DETECT_FLAG -- all other columns can be listed here 
from SLVs_Flagged s
cross apply (  
SELECT * 
FROM SLVs_Flagged
where PARAMETER_NAME = s.PARAMETER_NAME 
 and GW_LOCATION_ID = s.GW_LOCATION_ID 
 and Report_Result = s.ReportResult 
) T
group by s.PARAMETER_NAME, s.GW_LOCATION_ID

I think the following query is more intuitive to understand:

create table SLVs_Flagged (PARAMETER_NAME varchar(30), GW_LOCATION_ID varchar(30), Report_Result int, DETECT_FLAG char(1) )
go
insert into dbo.SLVs_Flagged 
values ('abc', 'cor1', 128, 'N'), ('abc', 'cor1', 12, 'Y')
, ('def', 'cor1', 500, 'Y'), ('def', 'cor1', 50, 'N')
go

;with s as (
select max(Report_Result) as Report_Result, PARAMETER_NAME,  GW_LOCATION_ID
from SLVs_Flagged 
group by PARAMETER_NAME, GW_LOCATION_ID)
SELECT s.Report_Result, s.PARAMETER_NAME,  s.GW_LOCATION_ID, t.DETECT_FLAG
FROM SLVs_Flagged t
inner join s 
on t.PARAMETER_NAME = s.PARAMETER_NAME and t.GW_LOCATION_ID = s.GW_LOCATION_ID and t.Report_Result = s.Report_Result
typo change
Source Link
jyao
jyao
  • 3.1k
  • 1
  • 13
  • 27

I think the following query is more intuitive to understand

select max(Report_Result) as ReportResult, PARAMETER_NAME, GW_LOCATION_ID,
t.DETECT_FLAG -- all other columns can be listed here 
from SLVs_Flagged s
cross apply (  
SELECT * 
FROM SLVs_Flagged
where PARAMETER_NAME = s.PARAMETER_NAME 
and GW_LOCATION_ID = s.GW_LOCATION_ID 
and Report_Result = s.ReportResult 
) T
group by s.PARAMETER_NAME, s.GW_LOCATION_ID

I think the following query is more intuitive to understand

select max(Report_Result) as ReportResult, PARAMETER_NAME, GW_LOCATION_ID,
t.DETECT_FLAG -- all other columns can be listed here 
from SLVs_Flagged s
cross apply (  
SELECT * 
FROM SLVs_Flagged
where PARAMETER_NAME = s.PARAMETER_NAME 
and GW_LOCATION_ID = s.GW_LOCATION_ID 
and Report_Result = s.ReportResult 
) T
group by PARAMETER_NAME, GW_LOCATION_ID

I think the following query is more intuitive to understand

select max(Report_Result) as ReportResult, PARAMETER_NAME, GW_LOCATION_ID,
t.DETECT_FLAG -- all other columns can be listed here 
from SLVs_Flagged s
cross apply (  
SELECT * 
FROM SLVs_Flagged
where PARAMETER_NAME = s.PARAMETER_NAME 
and GW_LOCATION_ID = s.GW_LOCATION_ID 
and Report_Result = s.ReportResult 
) T
group by s.PARAMETER_NAME, s.GW_LOCATION_ID
Source Link
jyao
jyao
  • 3.1k
  • 1
  • 13
  • 27

I think the following query is more intuitive to understand

select max(Report_Result) as ReportResult, PARAMETER_NAME, GW_LOCATION_ID,
t.DETECT_FLAG -- all other columns can be listed here 
from SLVs_Flagged s
cross apply (  
SELECT * 
FROM SLVs_Flagged
where PARAMETER_NAME = s.PARAMETER_NAME 
and GW_LOCATION_ID = s.GW_LOCATION_ID 
and Report_Result = s.ReportResult 
) T
group by PARAMETER_NAME, GW_LOCATION_ID