grouping from resultset?

• jsb12

  SSChasing Mays

  Points: 608

  More actions

  April 11, 2010 at 10:28 pm

  #140367

  I already had a post on this. I was trying to get results per database. I thought sp_msforeachdb would help, but it only executes the query per database, My problem was i needed top 20 per database.

  http://www.sqlservercentral.com/Forums/Topic896530-145-1.aspx#bm896770

  SELECT --TOP 20

  [Average IO] = (total_logical_reads + total_logical_writes) / qs.execution_count

  ,[Total IO] = (total_logical_reads + total_logical_writes)

  ,[Execution count] = qs.execution_count

  ,[Individual Query] = SUBSTRING (qt.text,qs.statement_start_offset/2,

  (CASE WHEN qs.statement_end_offset = -1

  THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2

  ELSE qs.statement_end_offset END - qs.statement_start_offset)/2)

  ,[Parent Query] = qt.text

  ,DatabaseName = DB_NAME(qt.dbid)

  FROM sys.dm_exec_query_stats qs

  CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt

  ORDER BY [Average IO] DESC;

  removing 'top 20' gives many results. I think grouping from the total result set is what I need.

  I wanted to get the top 20 results per database. Was not sure how to. Any help is appreciated.

• ColdCoffee

  SSC-Dedicated

  Points: 39972

  More actions

  April 11, 2010 at 10:44 pm

  #1149807

  1. Use ROW_NUMBER() function to dynamically allocate "ranks" to the result set (ORDER BY your column of course)

  2. Handle the top 20 in your WHERE clause, like, WHERE ROW_NUMBER BETWEEN 1 and 20

  Hope this helps..