×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Need help with a grouping select query

Need help with a grouping select query

Need help with a grouping select query

(OP)
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

RE: Need help with a grouping select query

"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

RE: Need help with a grouping select query

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

Lyndon

RE: Need help with a grouping select query

What is the primary key field in License table?

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

RE: Need help with a grouping select query

Check ROW_NUMBER() in the BOL

Borislav Borissov
VFP9 SP2, SQL Server

RE: Need help with a grouping select query

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

CODE --> SQL

Select License.*, Applications.ApplicationYear
From License, Applications
Where  License.ID=Applications.MasterID
  And License.Status='P'
  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)
Order By License.AssignedAgent, Applications.ApplicationYear, License.LastName, License.FirstName 

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

RE: Need help with a grouping select query

(OP)
Where License.ID=Applications.MasterID Is the join key

Lyndon

RE: Need help with a grouping select query

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

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

RE: Need help with a grouping select query

(OP)
sorry dhookom, the reply was for bborissov

Lyndon

RE: Need help with a grouping select query

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

RE: Need help with a grouping select query

(OP)
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

RE: Need help with a grouping select query

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

RE: Need help with a grouping select query

(OP)
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

RE: Need help with a grouping select query

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
www.syssoft-int.com

FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?

RE: Need help with a grouping select query

(OP)
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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close