Query to return specific number of rows

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Query to return specific number of rows

Author

Topic

Vergy39
Starting Member

15 Posts

Posted - 2012-06-14 : 16:34:01

I need to write a query that will return a specific number of rows by AssociateNumber. In some cases, an associate will have more then the required number and in some the associate will have less. currently, my query returns all records for all associates. I only want 10 records per associate if they have that many, and if they have less than 10, I want all of them. I tried the use Top 10 and RowCount, but failed. Any assistance is greatly appreciated. Below is what I currently have. I think I need to set up Cases, but a little confused.

"Select a.AssocID, b.tiCenter, b.NameFirst, b.NameLast, a.AcctDate, a.Division, a.AcctNum, a.Jcode, a.Credit
From csAudit.dbo.XSCM189 a
Left Join fwAIMS.dbo.tblAIMSProfile b On a.AssocID = b.AssociateNumber
Where JCode = 909 And AcctDate Between '2012-06-03' And '2012-06-09'
Group By a.AssocID, b.tiCenter, b.NameFirst, b.NameLast, a.AcctDate, a.Division, a.AcctNum, a.JCode, a.Credit
Order By a.AssocID"

Thanks
David

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-14 : 17:07:36

can you show some sample data without which we cant see which table has which data

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Vergy39
Starting Member

15 Posts

Posted - 2012-06-14 : 17:51:00

Thanks visakh16. I am new to this site so not sure if can paste attachements or not. Basically, we have a table that houses data regarding associates and the functions that they have performed. We want to audit a certain amount of these functions. This table is the XSCM189 table. This table only uses associate pin numbers, and we want to match them up with the associates name in the table called tbl.AIMSProfile. The xscm189 table lists the records by associate number. Hope this helps.

thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-17 : 12:38:31

just post data in below format

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources