Top 8 in a GROUP - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Top 8 in a GROUP

Author

Topic

joemama
Posting Yak Master

113 Posts

Posted - 2007-11-20 : 21:08:14

i have a query that groups 12 entries by user and sums then up to give me a total...no problem there...

here is what i really need to do

intead of summing up all 12 entries for each user i want to sum up only the top 8 (8 highest point totals out of the 12 entered)..any clue how to do it?

here is the select statement i am working with...any help would be appreciated

SELECT TOP (100) PERCENT dbo.WTournament_Results.TRUserID, dbo.WUsers.Suffix, dbo.WUsers.UserFirstName, dbo.WUsers.UserLastName, SUM(dbo.WTournament_Points.Points) AS [YTD Points]
FROM dbo.WTournaments INNER JOIN
dbo.WTournament_Results ON dbo.WTournaments.tournID = dbo.WTournament_Results.TID INNER JOIN dbo.WUsers ON dbo.WTournament_Results.TRUserID = dbo.WUsers.UserID INNER JOIN dbo.WTournament_Points ON dbo.WTournament_Results.TRPlace = dbo.WTournament_Points.Place

WHERE (dbo.WTournaments.TournYear = 2007) AND (dbo.WTournaments.TournYearInc = 1)
GROUP BY dbo.WTournament_Results.TRUserID, dbo.WUsers.Suffix, dbo.WUsers.UserFirstName, dbo.WUsers.UserLastName

ORDER BY [YTD Points] DESC

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2007-11-20 : 22:43:37

SELECT a.TRUserID, a.Suffix, a.UserFirstName, a.UserLastName, SUM(a.Points) AS [YTD Points]
(
select dbo.WTournament_Results.TRUserID, dbo.WUsers.Suffix, dbo.WUsers.UserFirstName, dbo.WUsers.UserLastName, dbo.WTournament_Points.Points
, Row_Number() over (partition by dbo.WTournament_Results.TRUserID, dbo.WUsers.Suffix, dbo.WUsers.UserFirstName, dbo.WUsers.UserLastName order by dbo.WTournament_Points.Points desc ) as RowID
FROM dbo.WTournaments INNER JOIN
dbo.WTournament_Results ON dbo.WTournaments.tournID = dbo.WTournament_Results.TID INNER JOIN dbo.WUsers ON dbo.WTournament_Results.TRUserID = dbo.WUsers.UserID INNER JOIN dbo.WTournament_Points ON dbo.WTournament_Results.TRPlace = dbo.WTournament_Points.Place

WHERE (dbo.WTournaments.TournYear = 2007) AND (dbo.WTournaments.TournYearInc = 1)
) a
where a.RowID <= 8

joemama
Posting Yak Master

113 Posts

Posted - 2007-11-21 : 11:20:51

Thanks for the help...when i try to run that query i get the following errors

Error in list of function arguments: '(' not recognized.
The OVER SQL construct or statement is not supported.

Incorrect syntax near "a"

any help would be appreciated

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-21 : 11:31:24

quote:
The OVER SQL construct or statement is not supported.

You are using SQL 2000 or 2005 ?

KH
[spoiler]Time is always against us[/spoiler]

joemama
Posting Yak Master

113 Posts

Posted - 2007-11-21 : 11:38:30

I am using 2005

quote:
Originally posted by khtan

quote:
The OVER SQL construct or statement is not supported.

You are using SQL 2000 or 2005 ?

KH
[spoiler]Time is always against us[/spoiler]

cognos79
Posting Yak Master

241 Posts

Posted - 2007-11-21 : 11:42:52

use SET ROWCount 8 statement first and then write your select query.

joemama
Posting Yak Master

113 Posts

Posted - 2007-11-21 : 12:03:08

sorry i am a newbie can you give me an example?

quote:
Originally posted by cognos79

use SET ROWCount 8 statement first and then write your select query.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-21 : 14:10:57

Change compatibility mode to "9" for the current database and try again.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-21 : 14:17:36

quote:
Originally posted by cognos79

use SET ROWCount 8 statement first and then write your select query.

Does that work for ALL group and not just the first?

E 12°55'05.25"
N 56°04'39.16"

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2007-11-21 : 14:22:19

There was a syntax Error in my original post. Use this:


 SELECT   a.TRUserID
		, a.Suffix
        , a.UserFirstName
        , a.UserLastName
        , SUM(a.Points) AS [YTD Points]
	FROM
		(
			select    dbo.WTournament_Results.TRUserID
					, dbo.WUsers.Suffix
                    , dbo.WUsers.UserFirstName
                    , dbo.WUsers.UserLastName
                    , dbo.WTournament_Points.Points
			        , Row_Number() over (partition by dbo.WTournament_Results.TRUserID, dbo.WUsers.Suffix, dbo.WUsers.UserFirstName, dbo.WUsers.UserLastName order by dbo.WTournament_Points.Points desc ) as RowID
			FROM 
				dbo.WTournaments 
			INNER JOIN
				dbo.WTournament_Results 
			ON		dbo.WTournaments.tournID = dbo.WTournament_Results.TID 
			INNER JOIN 
				dbo.WUsers 
			ON		dbo.WTournament_Results.TRUserID = dbo.WUsers.UserID 
			INNER JOIN 
				dbo.WTournament_Points 
			ON		dbo.WTournament_Results.TRPlace = dbo.WTournament_Points.Place
			WHERE 
					dbo.WTournaments.TournYear = 2007 
			AND		dbo.WTournaments.TournYearInc = 1
	)  a
where a.RowID <= 8
GROUP BY a.TRUserID, a.Suffix, a.UserFirstName, a.UserLastName

joemama
Posting Yak Master

113 Posts

Posted - 2007-11-21 : 20:38:42

Thanks again for the help but i get these errors...

The OVER SQL construct or statement is not supported.
and
Column "a.TRUSERID" is invalid in the select list because it is not contained in either an aggregrate function or the group by claus

any help would be appreciated

quote:

Originally posted by Vinnie881

There was a syntax Error in my original post. Use this:


 SELECT   a.TRUserID
		, a.Suffix
        , a.UserFirstName
        , a.UserLastName
        , SUM(a.Points) AS [YTD Points]
	FROM
		(
			select    dbo.WTournament_Results.TRUserID
					, dbo.WUsers.Suffix
                    , dbo.WUsers.UserFirstName
                    , dbo.WUsers.UserLastName
                    , dbo.WTournament_Points.Points
			        , Row_Number() over (partition by dbo.WTournament_Results.TRUserID, dbo.WUsers.Suffix, dbo.WUsers.UserFirstName, dbo.WUsers.UserLastName order by dbo.WTournament_Points.Points desc ) as RowID
			FROM 
				dbo.WTournaments 
			INNER JOIN
				dbo.WTournament_Results 
			ON		dbo.WTournaments.tournID = dbo.WTournament_Results.TID 
			INNER JOIN 
				dbo.WUsers 
			ON		dbo.WTournament_Results.TRUserID = dbo.WUsers.UserID 
			INNER JOIN 
				dbo.WTournament_Points 
			ON		dbo.WTournament_Results.TRPlace = dbo.WTournament_Points.Place
			WHERE 
					dbo.WTournaments.TournYear = 2007 
			AND		dbo.WTournaments.TournYearInc = 1
	)  a
where a.RowID <= 8

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-11-21 : 20:42:36

Over syntax is only on SQL 2005? DO you have SQL 2005 Server?

Poor planning on your part does not constitute an emergency on my part.

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2007-11-21 : 21:19:02

Did you do what peso suggested and change your compatability mode?

joemama
Posting Yak Master

113 Posts

Posted - 2007-11-21 : 23:42:11

compatability is at 90

quote:
Originally posted by Vinnie881

Did you do what peso suggested and change your compatability mode?

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2007-11-21 : 23:53:21

quote:
Originally posted by joemama

Thanks again for the help but i get these errors...

The OVER SQL construct or statement is not supported.
and
Column "a.TRUSERID" is invalid in the select list because it is not contained in either an aggregrate function or the group by claus

any help would be appreciated

Re-copy the query, I corrected the issue with trUserID, but the over error is not making sense. You are certain you are running Microsoft Sql Server 2005 correct?

joemama
Posting Yak Master

113 Posts

Posted - 2007-11-22 : 11:31:38

IT WORKS!
you guys rock...

the over error was only pertaining to "graphical view"

thanks you really helped me out...i appreciate it

quote:
Originally posted by Vinnie881

quote:
Originally posted by joemama

Thanks again for the help but i get these errors...

The OVER SQL construct or statement is not supported.
and
Column "a.TRUSERID" is invalid in the select list because it is not contained in either an aggregrate function or the group by claus

any help would be appreciated

Re-copy the query, I corrected the issue with trUserID, but the over error is not making sense. You are certain you are running Microsoft Sql Server 2005 correct?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources