Query Help - 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)

Query Help

Author

Topic

hibbert
Starting Member

2 Posts

Posted - 2008-03-05 : 07:30:16

Hi,
First time poster in need of query help.

I am working on a hockey database and I am trying to write a query that will tell me which NHL team a player spent the most seasons with. The stats table has a row for each season he played, and each row has a team id column and a person id (as well as the actual stats). Suppose a player spent 5 seasons in Toronto, but only one in Detroit and another 2 in Boston, I want my query to return the team id for Toronto along with the person_id.

I can't post any code, because I have no clue how to where to start!

Thanks in advance.

Hib

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-03-05 : 08:24:14

May be this:-

SELECT tmp.personid,tmp.teamid
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY t.personid ORDER BY t.Occurance DESC) AS RowNo,
t.personid,
t.teamid
FROM
(
SELECT personid,teamid,COUNT(*) AS Occurance
FROM Stats
GROUP BY personid,teamid
)t
)tmp
WHERE tmp.RowNo=1

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-05 : 08:24:58

Since we don't know ANYTHING about your database and/or structure, here is a long-shot

-- Prepare sample data
DECLARE	@Sample TABLE
	(
		TeamID INT,
		Season VARCHAR(40),
		PersonID INT
	)

INSERT	@Sample
SELECT	3, '1997', 1 UNION ALL
SELECT	2, '1998', 1 UNION ALL
SELECT	2, '1999', 1 UNION ALL
SELECT	2, '2000', 1 UNION ALL
SELECT	1, '2001', 1 UNION ALL
SELECT	1, '1997', 2 

-- Show the expected output
SELECT		q.PersonID,
		q.TeamID,
		q.Seasons
FROM		(
			SELECT		PersonID,
					TeamID,
					COUNT(*) AS Seasons,
					RANK() OVER (PARTITION BY PersonID ORDER BY COUNT(*) DESC) AS RecID
			FROM		@Sample
			GROUP BY	PersonID,
					TeamID
		) AS q
WHERE		q.RecID = 1
ORDER BY	q.PersonID,
		q.TeamID

You really, REALLY, need to read this blog post
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-05 : 09:03:55

Ayamas, I think it is time for you to realize that using MAX for several columns DO NOT make the answer right.
There is absolutely no guarantee that the two MAX values derives from same record!

-- Prepare sample data
DECLARE	@Sample TABLE (CatID INT, ProdID INT, ProdName VARCHAR(200))

INSERT	@Sample
SELECT	1, 1, 'Zweeck' UNION ALL
SELECT	1, 2, 'Aardvark' UNION ALL
SELECT	2, 3, 'SQLTeam' UNION ALL
SELECT	2, 4, 'Yak'

SELECT		CatID,
		MAX(ProdID) AS ProdID,
		MAX(ProdName) AS ProdName
FROM		@Sample
GROUP BY	CatID

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-05 : 09:06:05

quote:
Originally posted by ayamas

One more solution from me

Use this sample data Ayamas and see which result you get

declare @tbl as table(teamid int,season varchar(40),personid int)
insert into @tbl
select 3, '1997', 1 union all
select 2, '1998', 1 union all
select 2, '1999', 1 union all
select 2, '2000', 1 union all
select 1, '2001', 1 union all
select 1, '1997', 2

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

ayamas
Aged Yak Warrior

552 Posts

Posted - 2008-03-05 : 09:19:47

quote:
Originally posted by Peso

Ayamas, I think it is time for you to realize that using MAX for several columns DO NOT make the answer right.
There is absolutely no guarantee that the two MAX values derives from same record!

-- Prepare sample data
DECLARE @Sample TABLE (CatID INT, ProdID INT, ProdName VARCHAR(200))

INSERT @Sample
SELECT 1, 1, 'Zweeck' UNION ALL
SELECT 1, 2, 'Aardvark' UNION ALL
SELECT 2, 3, 'SQLTeam' UNION ALL
SELECT 2, 4, 'Yak'

SELECT CatID,
MAX(ProdID) AS ProdID,
MAX(ProdName) AS ProdName
FROM @Sample
GROUP BY CatID

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

Sorry missed on than one & thanks Peso for the catch.I will be deleting my answer.

hibbert
Starting Member

2 Posts

Posted - 2008-03-05 : 14:39:33

Thanks for the help.

I have read the link provided about posting details and etiquette, and will respect them next time. Sorry for the incomplete info.

Thanks again,

Hib

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources