Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Need help with a grouping select query

Status
Not open for further replies.

LyndonOHRC

Programmer
Sep 8, 2005
603
US
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
Code:
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
 
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
 
Yes the top fifty records for each assigned agent while in appYear, lastname, firstname order.

Lyndon
 
Check ROW_NUMBER() in the BOL

Borislav Borissov
VFP9 SP2, SQL Server
 
Borislav is more of a SQL geek than I am so I would go old school with SQL something like:

SQL:
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
 
Where License.ID=Applications.MasterID Is the join key


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

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016
 
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
 
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
 
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
 
Still getting syntax error in query expression
Code:
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
 
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

FAQ219-2884
FAQ181-2886
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top