Jump to content
Dan Munns

Limit group by to 5

Recommended Posts

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: 

image.thumb.png.3bfd83c36823fec4d6297694bbdc5b68.png

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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

@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. :)

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×
×
  • Create New...