Numerating Grouped Query Results

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

General SQL Server Forums

New to SQL Server Programming

Numerating Grouped Query Results

Author

Topic

QueryUser777
Starting Member

3 Posts

Posted - 2012-11-07 : 14:02:29

Hi everyone.
I need some help in building a field that will yield numerating my query results based on a grouped fields...my end result would need to look something like below...not sure if its even possible...but couldn't hurt to ask..

Agent Team WeekEnding RecordID Score Audit#
Mike RedTeam 11/03/2012 222 99 1
Mike RedTeam 11/03/2012 985 81 2
Mike RedTeam 11/03/2012 1002 87 3
Tony BlueTeam 11/03/2012 647 60 1
Tony BlueTeam 11/03/2012 788 75 2
Mike RedTeam 11/09/2012 1215 91 1
John GreenTeam 11/09/2012 1375 95 1
John GreenTeam 11/09/2012 1415 98 2

I need to be able to create the field labeled "Audit#"...the query results need to be numerated by the number of times a record is grouped (ex. Mike, RedTeam, 11/03/2012 has three recordid and so the Audit# will show 1,2,3)..Thank you in advance for any insight or direction on this issue.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-07 : 14:05:56

The row_number function is ideally suited for this purpose. Available on SQL 2005 or later

SELECT
	*,
	ROW_NUMBER() OVER (PARTITION BY Agent, Team, WeekEnding ORDER BY RecordId) AS [Audit#]
FROM
	YourTable;

QueryUser777
Starting Member

3 Posts

Posted - 2012-11-07 : 14:13:27

Wow, thank you for the prompt response sunitabeck. the query i am building is in Access 2007 and can't seem to find the row_number function...thoughts?

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-11-07 : 15:30:19

The ROW_NUMBER() function is a SQL Server function, as this is a MS SQL Server forum. To do this in Access, I think you can join a table to itself to get similar results.

SELECT t1.*,count(*)
FROM Table t1
INNER JOIN Table t2 ON
t1.Agent = t2.Agent
and t1.Team = t2.Team
and t1.Weekending = t2.Weekending
and t1.RecordID >= t2.RecordID

Jim

Everyday I learn something that somebody else already knew

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources