Limit group by to 5

[Dan Munns]

Hi all,

I have some (frankly awful I am sure) SQL for a widget we use for QA. 

SELECT h_pk_reference, h_summary, h_datelogged, h_resolvedby_username, h_source_type, h_fk_team_name, ROW_NUMBER() OVER (PARTITION BY h_resolvedby_username) AS 'ANALYSTS' 
FROM h_itsm_requests 
WHERE h_pk_reference LIKE '%IN%' AND h_status = 'status.closed' AND (h_datelogged >= LAST_DAY(NOW() - INTERVAL 2 MONTH) + INTERVAL 1 DAY) AND (h_datelogged < LAST_DAY(NOW() - INTERVAL 1 MONTH)) AND (h_source_type <> 'Autoresponder' AND h_source_type <> 'Email' AND h_source_type <> 'Self Service') AND (h_summary NOT LIKE '%Password Reset%' AND h_summary NOT LIKE '%Account Unlocked%') AND (h_fk_team_name = 'IT Service Desk' OR h_fk_team_name = 'IT Infrastructure' OR h_fk_team_name = 'IT TEAM 3' OR h_fk_team_name = 'IT TEAM 4' OR h_fk_team_name = 'IT TEAM 5' OR h_fk_team_name = 'IT TEAM 6') 
ORDER BY ANALYSTS LIMIT 40

This produces the below list in a widget: 

This is better than what I did have (in that it shows a wider group of users), which was 30 completely random requests from within the filter but what I really want is a random list which only shows a max of say 5 entries per analyst.

I am sure this is possible and that my SQL skills leave much to be desired but can anyone help me out with this? 

Thanks,

Dan

[Dan Munns]

Anybody?

[Dan Munns]

@AlexTumber dont suppose you have any ideas on this? 

[SamS]

Hi @Dan Munns,

The following SQL will get you your random 5 entries (of the filtered) per analyst.

SELECT * FROM (
SELECT
	h_pk_reference, h_summary, h_datelogged, h_resolvedby_username, h_source_type, h_fk_team_name
	, ROW_NUMBER() OVER (PARTITION BY h_resolvedby_username) AS row_num 
FROM h_itsm_requests 
WHERE
	h_pk_reference LIKE '%IN%' AND h_status = 'status.closed'
	AND (h_datelogged >= LAST_DAY(NOW() - INTERVAL 2 MONTH) + INTERVAL 1 DAY)
	AND (h_datelogged < LAST_DAY(NOW() - INTERVAL 1 MONTH)) 
	AND (h_source_type NOT IN ('Autoresponder', 'Email', 'Self Service')
	AND (h_summary NOT LIKE '%Password Reset%' AND h_summary NOT LIKE '%Account Unlocked%')
	AND (h_fk_team_name IN ('IT Service Desk', 'IT Infrastructure', 'IT TEAM 3', 'IT TEAM 4', 'IT TEAM 5', 'IT TEAM 6') 
) AS abc
WHERE row_num < 6
LIMIT 100

The LIMIT 100 at the end is because there should not be more than 100 results in the widget

The row_num < 6 is because the rows start numbering with 1, so first five is < 6.

[Dan Munns]

Thanks @SamS Ill give it a spin

[Dan Munns]

@SamS works spot on (*cough* once I added the missing brackets *cough*)

Thanks for tidying up the SQL as well. Learnt something so its a win all round.