select max ? - 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

General SQL Server Forums

New to SQL Server Programming

select max ?

Author

Topic

Davide70
Starting Member

6 Posts

Posted - 2012-02-16 : 06:21:34

Hello to all experts,
we have the following table:

T(id, begin, end, team, men, women, rank)

Now we want to get men , women and rank
for team xx
where end is the maximum value in the table for that team.
We tried the following query:

SELECT MAX(end), men, women, rank
FROM T
where team=xx
GROUP BY men, women, rank

but we get more rows then expected, not just the one corresponding to max end (in this case 2 rows for the same rank):

End____Men____Women___Rank
862194__2_______0_______4
1892037_1_______0_______4
1302560_3_______0______14
1450814_2_______0______14

same problem using having end=max(end). Suggestions?
Thank you very much,
Davide

rajan.nithin
Starting Member

42 Posts

Posted - 2012-02-16 : 07:32:50

Try This:

SELECT MAX(end), men, women, rank
FROM T
where team=xx
GROUP BY team,men, women, rank

Davide70
Starting Member

6 Posts

Posted - 2012-02-16 : 09:01:08

Thank you,
but the result is the same..

quote:
Originally posted by rajan.nithin

Try This:

SELECT MAX(end), men, women, rank
FROM T
where team=xx
GROUP BY team,men, women, rank

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-02-16 : 09:06:26

What should be the output for the example you showed?

You may be able use something like what I am showing below, or may be you need to use a summation on men, women etc. In the query below I have included only team in the PARTITION clause - you may need to add other columns depending on your requirement.

SELECT
	END, men, women, RANK
FROM
(
	SELECT
		End
		men,
		women,
		RANK,
		ROW_NUMBER() OVER (PARTITION BY team ORDER BY END DESC) AS RN
	FROM
		T
	WHERE
		team = xx
) s
WHERE RN = 1

Davide70
Starting Member

6 Posts

Posted - 2012-02-16 : 10:27:51

Only the 2nd and 4th rows, those with max End..
Thank you,

quote:
Originally posted by sunitabeck

What should be the output for the example you showed?

You may be able use something like what I am showing below, or may be you need to use a summation on men, women etc. In the query below I have included only team in the PARTITION clause - you may need to add other columns depending on your requirement.
SELECT
END, men, women, RANK
FROM
(
SELECT
End
men,
women,
RANK,
ROW_NUMBER() OVER (PARTITION BY team ORDER BY END DESC) AS RN
FROM
T
WHERE
team = xx
) s
WHERE RN = 1

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-02-16 : 12:48:38

Change the partition by clause to include rank also

ROW_NUMBER() OVER (PARTITION BY team, rank ORDER BY END DESC) AS RN

X002548
Not Just a Number

15586 Posts

Posted - 2012-02-16 : 13:16:00

SELECT * MAX(end), men, women, rank
FROM T t1
-- where team=xx
AND EXISTS (SELECT * FROM T t2 WHERE t1.team = t2.team
GROUP BY team
HAVING t1.End = MAX(t2.End))

???

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources