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
AikiRyuAsked:
Who is Participating?
 
chapmandewCommented:
how bout this....

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)
and convert(char(10),hn_qos_data_0009.Sampleavg = (select max(convert(char(10),Sampleavg) from hn_qos_data_0009  g where g.idvalue = hn_qos_data_0009.idvalue)
0
 
chapmandewCommented:
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
0
 
AikiRyuAuthor Commented:
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
0
 
AikiRyuAuthor Commented:

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
0
 
chapmandewCommented:
great!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.