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

Jikol
Starting Member

13 Posts

Posted - 2008-07-18 : 10:17:58

I have tried to figure this one out, but can't crack it. It seems like it should be easy, but it's past me.

I have a view which shows Climber, date, and Count of problems(climbs). What I'm after is showing the maximum number of climbs done in one day. So.....

if Climber A has done 4 climbs on 7/14/8 and 10 climbs on 7/15/8 the query will return the 10 climbs only. Right now it just shows the total climbs done on any given date(all of them). Here is the query

SELECT Climber, Expr2 AS ClimbsPerDate, Date
FROM MostClimbsInADayView
GROUP BY Climber, Expr2, Date

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-18 : 10:21:50

SELECT Climber, MAX(Expr2) AS maxclimbvalue
FROM MostClimbsInADayView
GROUP BY Climber

Jikol
Starting Member

13 Posts

Posted - 2008-07-18 : 10:25:00

That gave part of the result that I was looking for. Thanks.

But....

How do I get it to show the date of that particular day. When I add the date field to the query I'm back where I started with show every date for the climber.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-18 : 10:29:18

quote:
Originally posted by Jikol

That gave part of the result that I was looking for. Thanks.

But....

How do I get it to show the date of that particular day. When I add the date field to the query I'm back where I started with show every date for the climber.

so are you telling that you need to return same max climb count value for each climber for each of the dates?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-18 : 10:31:16

use this if your reqmnt is above

SELECT m.Climber,m.maxclimbvalue,t.Date
FROM
(
SELECT Climber, MAX(Expr2) AS maxclimbvalue
FROM MostClimbsInADayView
GROUP BY Climber)m
INNER JOIN 
(
SELECT DISTINCT Climber, Date
FROM MostClimbsInADayView)t
ON t.Climber=m.Climber

Jikol
Starting Member

13 Posts

Posted - 2008-07-18 : 10:32:36

Not for each of the dates. I just want to know the date that the climber has the most climbs done. So if the climber has 25 days of climbing I want to know which date he climbed the most climbs as well as the number of climbs he did that day.

This is for each climber in the database.

Jikol
Starting Member

13 Posts

Posted - 2008-07-18 : 10:36:00

That was closer but I'm still getting a result for each climbing day.

Jikol
Starting Member

13 Posts

Posted - 2008-07-18 : 13:15:46

Visakh16,

any more clues??

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-07-18 : 13:26:33

Try this. I used MAX on the date incase there are mutiple days that the climber had the same "most" climbs:

SELECT 
	m.Climber,
	m.maxclimbvalue,
	MAX(t.Date)
FROM
	(
		SELECT Climber, MAX(Expr2) AS maxclimbvalue
		FROM MostClimbsInADayView
		GROUP BY Climber
	) AS m
INNER JOIN 
	MostClimbsInADayView AS T
	ON T.Climber = m.Climber
	AND T.Expr2 = m.maxclimbvalue
GROUP BY
	m.Climber,
	m.maxclimbvalue

Jikol
Starting Member

13 Posts

Posted - 2008-07-18 : 16:45:27

Awesome!!!

That worked Perfect. Thanks!!!!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources