Link to home
Start Free TrialLog in
Avatar of AikiRyu
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_data.source         ) as Source    
        ,convert(char(10),hn_qos_data_0009.Sampleavg) as SampleAvg
        ,                 s_qos_data.created          as Created
        ,convert(char(20),s_qos_data.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 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),@sourceName)
     
      select  
       @sourceName                                      as Source    
       -- ,convert(char(10),hn_qos_data_0009.SampleAvg) as SampleAvg
          ,                 s_qos_data.created          as Created
          ,convert(char(20),s_qos_data.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 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
Avatar of chapmandew
chapmandew
Flag of United States of America image

i think you just needed to correlate your query.

 select  convert(char(24),s_qos_data.source         ) as Source    
        ,convert(char(10),hn_qos_data_0009.Sampleavg) as SampleAvg
        ,                 s_qos_data.created          as Created
        ,convert(char(20),s_qos_data.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.idvalue = s_qos_data.idvalue)
   go
Avatar of AikiRyu
AikiRyu

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_data.source         ) as Source    
      ,convert(char(10),hn_qos_data_0009.Sampleavg) as SampleAvg
      ,                 s_qos_data.created          as Created
      ,convert(char(20),s_qos_data.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
ASKER CERTIFIED SOLUTION
Avatar of chapmandew
chapmandew
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of AikiRyu

ASKER


I added "distinct" and it looks good!

-- 702 rows
select distinct  
       convert(char(24),s_qos_data.source         ) as Source    
      ,convert(char(10),hn_qos_data_0009.Sampleavg) as SampleAvg
      ,                 s_qos_data.created          as Created
      ,convert(char(20),s_qos_data.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)
and hn_qos_data_0009.SampleAvg = (select max(SampleAvg) from hn_qos_data_0009 g
                             where g.table_id = hn_qos_data_0009.table_id)
order by source, target
great!