Need help with a grouping select query

[LyndonOHRC]

I have the query below that selects the correct data. We need to modify it for another output and it's beyond my skill level.

This time I need to group it by License.AssignedAgent and only pull the "Top 50" of each group. Also, the "Order By" clause I'm using is important within each group; we need to see the newest Applications.ApplicationYear's first.


Any help much appreciated. Lyndon

Select License.*, Applications.ApplicationYear
	From License, Applications
	Where  License.ID=Applications.MasterID
	And License.Status='P'
	Order By License.AssignedAgent, Applications.ApplicationYear, License.LastName, License.FirstName

Lyndon

 

[dhookom]

TOP 50" assumes you have a column/field that identifies which 50. Can we assume you want the top 50 ... for each AssignedAgent?

I expect you could use a subquery in the WHERE clause but the SQL gurus here probably have a more efficient solution.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[LyndonOHRC]

Yes the top fifty records for each assigned agent while in appYear, lastname, firstname order.

Lyndon

 

[dhookom]

What is the primary key field in License table?

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[bborissov]

Check ROW_NUMBER() in the BOL

Borislav Borissov
VFP9 SP2, SQL Server

 

[dhookom]

Borislav is more of a SQL geek than I am so I would go old school with SQL something like:

Select License.*, Applications.ApplicationYear
From License, Applications
Where  License.ID=Applications.MasterID
  And License.Status='P'
[highlight #FCE94F]  And License.PrimaryKey IN 
    (SELECT TOP 50 L.PrimaryKey 
     FROM License L JOIN Applications A on L.ID = A.MasterID
     WHERE L.AssignedAgent = License.AssignedAgent
     Order By A.ApplicationYear DESC, L.LastName, L.FirstName)[/highlight]
Order By License.AssignedAgent, Applications.ApplicationYear, License.LastName, License.FirstName

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[LyndonOHRC]

Where License.ID=Applications.MasterID Is the join key


Lyndon

 

[dhookom]

Lyndon,
I understand that. Did you have any answers or additional questions?

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[LyndonOHRC]

sorry dhookom, the reply was for bborissov

Lyndon

 

[dhookom]

bborissov was simply suggesting you use the ROW_NUMBER() functionality in Books On Line (BOL). It works similar to the suggestion I posted however it might be way cooler.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[LyndonOHRC]

Thanks Duane, I just tried the example this error:
in query expression . (Error 3075)

I haven't spotted a misspelled column name.

Lyndon

 

[LyndonOHRC]

Here's another idea. My primary key is the auto-populate numeric column "ID". Is the "IN" clause supposed to evaluate a comma delimited set of some kind?

Sorry if I'm grasping...

Lyndon

 

[dhookom]

Your SQL is missing the ID field in the subquery.

BTW: post the SQL view of your query in text.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[LyndonOHRC]

Still getting syntax error in query expression

Select License.*, Applications.ApplicationYear
	From License, Applications
	Where  License.ID=Applications.MasterID
	And License.Status='PENDING'
	And Applications.ApplicationYear >= '2018'
	And License.id IN 
	(
		SELECT TOP 50 License.id
			FROM License L JOIN Applications A on L.ID = A.MasterID
			WHERE L.AssignedAgent = License.AssignedAgent
 			Order By A.ApplicationYear, L.LastName, L.FirstName
	)
	Order By License.AssignedAgent, Applications.ApplicationYear, License.LastName, License.FirstName

Lyndon

 

[fredericofonseca]

by the error .jpg this is running in Microsoft Access, not SQL Server.
As such there are further rules and not all functionality is available.

This should have been mentioned first and will change how the SQL is built.

the code above does work correctly on SQL Server.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[LyndonOHRC]

So sorry! I forgot to take this to the Access Forum; I thought i had.

Thread continued here

Thank you. And sorry for the inconvenience.

Lyndon