Improve a query - 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)

Improve a query

Author

Topic

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2009-08-01 : 18:51:07

Hi.
I have a table with some instance data:


Name		Score		Day
-------------------------------------
Peter		120		1
Peter		130		2
Peter		130		3
Mary		85		2
Mary		95		2
David		90		1

And I want result similar to


Name		High Personal Score	First High Score Day
-----------------------------------------------------------
Peter			130				2
Mary			95				2
David			90				1

Please post any other query except following query.



DECLARE	@Sample TABLE
	(
		Name CHAR(15) NOT NULL,
		Score TINYINT NOT NULL,
		Day   TINYINT NOT NULL
	)

INSERT	@Sample
SELECT	'Peter', 120, 1 UNION ALL
SELECT	'Peter', 130, 2 UNION ALL
SELECT  'Peter', 130, 3 UNION ALL
SELECT	'Mary' , 85 , 2 UNION ALL
SELECT	'Mary' , 95 , 2 UNION ALL
SELECT	'David', 90 , 1
 
 

SELECT Name,
	Score=(SELECT MAX(Score) 
                   FROM @Sample 
                   WHERE Name=d.Name),
	day=(SELECT TOP 1 day 
                 FROM @Sample AS t1 
                 WHERE Name=d.Name 
                 AND Score >=ALL (SELECT Score 
				  FROM @Sample AS t2 
				  WHERE t1.Name=t2.Name)
                 ORDER BY day)
            
FROM(
     SELECT DISTINCT Name
     FROM @Sample
     ) AS d
ORDER BY name DESC

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-01 : 18:58:13

[code]SELECT Name,
Score,
Day
FROM (
SELECT Name,
Score,
Day,
ROW_NUMBER() OVER (PARTITION BY Name ORDER BY Score DESC, Day) AS recID
FROM @Sample
) AS d
WHERE recID = 1[/code]

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources