max of sums with cte?

• memymasta

  SSCrazy

  Points: 2373

  More actions

  January 29, 2013 at 4:32 am

  #273522

  Hi, Im trying to create a high score list from a log table. I want to retrieve the personal best of a specific user (userid=1) and overall leader as well based on daily activity.

  The desired result from the table below would be:

  specific_user, specific_high, specific_date, overall_user, overall_high, overall_date

  1, 2, '2012-01-01', 3,3, '2012-01-04'

  BEGIN TRAN

  CREATE TABLE [#tasks_log](

  [id] [int] NOT NULL,

  [userid] [int] NULL,

  [recorddate] [int] NULL

  ) ON [PRIMARY]

  GO

  INSERT INTO [#tasks_log] (id, userid, recorddate)

  SELECT 1,1,'2012-01-01' UNION ALL

  SELECT 2,1,'2012-01-01' UNION ALL

  SELECT 3,1,'2012-01-02' UNION ALL

  SELECT 4,2,'2012-01-01' UNION ALL

  SELECT 1,2,'2012-01-01' UNION ALL

  SELECT 2,3,'2012-01-04' UNION ALL

  SELECT 3,3,'2012-01-04' UNION ALL

  SELECT 1,3,'2012-01-04' UNION ALL

  SELECT 2,3,'2012-01-03' UNION ALL

  SELECT 3,3,'2012-01-03'

  ROLLBACK

  Ive managed to get the values for specific user, but not for the overall leader.

  ;WITH CTE_mycount (specific_user,daily_high,specific_date)

  AS

  (

  SELECT userid as specific_user, COUNT(id) as daily_high, CAST(recorddate AS DATE) as specific_date FROM [#tasks_log] WHERE userid=1 GROUP BY userid, CAST(recorddate AS DATE)

  )

  SELECT TOP 1 specific_user, MAX(daily_high) as specific_high, specific_date FROM CTE_mycount GROUP BY specific_user, specific_date ORDER BY specific_high DESC;

  EDIT: Here's the code for overall all time high:

  ;WITH CTE_mycount (overall_user,overall_high,overall_date)

  AS

  (

  SELECT userid as specific_user, COUNT(id) as daily_high, CAST(recorddate AS DATE) as specific_date FROM [#tasks_log] GROUP BY userid, CAST(recorddate AS DATE)

  )

  SELECT TOP 1 overall_user, MAX(overall_high) as overall_high, overall_date FROM CTE_mycount GROUP BY overall_user, overall_date ORDER BY overall_high DESC;

  Is to wise to retrieve both specific and overall record from a single query or is it better to split it up in two?

  Thanks for your time

• mickyT

  SSChampion

  Points: 10360

  More actions

  January 29, 2013 at 12:07 pm

  #1582293

  Hi

  I think something like this may do what you want.

  ;with countbyuserday as (

  SELECT userid, recorddate, count(*) numrec

  FROM [#tasks_log]

  GROUP BY userid, recorddate

  ),

  seqbymaxnum as (

  SELECT userid, recorddate, numrec, ROW_NUMBER() OVER (PARTITION BY userid ORDER BY numrec DESC) seq

  FROM countbyuserday

  )

  select * from seqbymaxnum where seq = 1