AikiRyu
asked on
SQL Server query help
I need help with a query. For each source, I want to print some data for the maximum value of created.
goal
for each source
print data for max(created)
-- This is a start but is not correct.
select convert(char(24),s_qos_dat
,convert(char(10),hn_qos_d
, s_qos_data.created as Created
,convert(char(20),s_qos_da
from s_qos_data
join hn_qos_data_0009
on s_qos_data.table_id = hn_qos_data_0009.table_id
where qos = 'qos_disk_usage_perc'
and created = (select max(created) from s_qos_data where qos = 'qos_disk_usage_perc')
go
-- This is closer but still not right. As written, 628 rows are produced but SampleAvg is not
-- in the query. When I uncomment SampleAvg, I get 2672 rows.
use nimbusSlm
set nocount on
declare @sourceName varchar(50)
declare sourceCursor cursor for select distinct source from s_qos_data order by source
open sourceCursor
fetch next from sourceCursor into @sourceName
while @@fetch_status = 0
begin
select @sourceName = convert(char(24),@sourceNa
select
@sourceName as Source
-- ,convert(char(10),hn_qos_d
, s_qos_data.created as Created
,convert(char(20),s_qos_da
from s_qos_data join hn_qos_data_0009
on s_qos_data.table_id = hn_qos_data_0009.table_id
where qos = 'qos_disk_usage_perc'
and created = (select max(created)
from s_qos_data where qos = 'qos_disk_usage_perc'
and s_qos_data.source = @sourceName)
and qos = 'qos_disk_usage_perc'
and s_qos_data.source = @sourceName
group by source, created, target --, sampleAvg
-- having sampleAvg=max(SampleAvg)
fetch next from sourceCursor into @sourceName
end
close sourceCursor
deallocate sourceCursor
ASKER
Thanks, we're getting closer!
-- Without "distinct," this query produces 17550 rows with many duplicates
-- When I include "distinct," query produces 2967 rows but I still have too many values.
-- I only want the maximum SampleAvg for this source
--
-- Source SampleAvg Created Target
-- ------------------------ ---------- ----------------------- --------------------
-- allele 39.66 2008-12-11 10:31:00 /var
-- allele 39.67 2008-12-11 10:31:00 /var
-- allele 39.69 2008-12-11 10:31:00 /var
-- allele 39.70 2008-12-11 10:31:00 /var
-- allele 39.71 2008-12-11 10:31:00 /var
-- allele 39.73 2008-12-11 10:31:00 /var
-- allele 39.74 2008-12-11 10:31:00 /var ** I only want this row **
select distinct
convert(char(24),s_qos_dat a.source ) as Source
,convert(char(10),hn_qos_d ata_0009.S ampleavg) as SampleAvg
, s_qos_data.created as Created
,convert(char(20),s_qos_da ta.target ) as Target
from s_qos_data
join hn_qos_data_0009
on s_qos_data.table_id = hn_qos_data_0009.table_id
where qos = 'qos_disk_usage_perc'
and created =
(select max(created) from s_qos_data g
where qos = 'qos_disk_usage_perc'
and g.table_id = s_qos_data.table_id)
order by source
go
-- Without "distinct," this query produces 17550 rows with many duplicates
-- When I include "distinct," query produces 2967 rows but I still have too many values.
-- I only want the maximum SampleAvg for this source
--
-- Source SampleAvg Created Target
-- ------------------------ ---------- ----------------------- --------------------
-- allele 39.66 2008-12-11 10:31:00 /var
-- allele 39.67 2008-12-11 10:31:00 /var
-- allele 39.69 2008-12-11 10:31:00 /var
-- allele 39.70 2008-12-11 10:31:00 /var
-- allele 39.71 2008-12-11 10:31:00 /var
-- allele 39.73 2008-12-11 10:31:00 /var
-- allele 39.74 2008-12-11 10:31:00 /var ** I only want this row **
select distinct
convert(char(24),s_qos_dat
,convert(char(10),hn_qos_d
, s_qos_data.created as Created
,convert(char(20),s_qos_da
from s_qos_data
join hn_qos_data_0009
on s_qos_data.table_id = hn_qos_data_0009.table_id
where qos = 'qos_disk_usage_perc'
and created =
(select max(created) from s_qos_data g
where qos = 'qos_disk_usage_perc'
and g.table_id = s_qos_data.table_id)
order by source
go
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I added "distinct" and it looks good!
-- 702 rows
select distinct
convert(char(24),s_qos_dat
,convert(char(10),hn_qos_d
, s_qos_data.created as Created
,convert(char(20),s_qos_da
from s_qos_data
join hn_qos_data_0009
on s_qos_data.table_id = hn_qos_data_0009.table_id
where qos = 'qos_disk_usage_perc'
and created = (select max(created) from s_qos_data g
where qos = 'qos_disk_usage_perc'
and g.table_id = s_qos_data.table_id)
and hn_qos_data_0009.SampleAvg
where g.table_id = hn_qos_data_0009.table_id)
order by source, target
great!
select convert(char(24),s_qos_dat
,convert(char(10),hn_qos_d
, s_qos_data.created as Created
,convert(char(20),s_qos_da
from s_qos_data
join hn_qos_data_0009
on s_qos_data.table_id = hn_qos_data_0009.table_id
where qos = 'qos_disk_usage_perc'
and created = (select max(created) from s_qos_data g where qos = 'qos_disk_usage_perc' and g.idvalue = s_qos_data.idvalue)
go