Show Max number in 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)

Show Max number in Query

Author

Topic

JezLisle
Posting Yak Master

132 Posts

Posted - 2008-10-01 : 05:42:19

The code below shows all the data set in from the Query, how can I alter the query to show me just the Maximum number??


SELECT     MAX(jez.WL_DATA.[Weeks Waiting]) AS Max
FROM         jez.WL_RANGES LEFT OUTER JOIN
                      jez.WL_DATA ON jez.WL_DATA.[Weeks Range] = jez.WL_RANGES.[weeks range] AND jez.WL_DATA.[Waiting List Service] = 'PHYSIO' AND 
                      jez.WL_DATA.Month = 'September' AND jez.WL_DATA.Year = '2008' AND jez.WL_DATA.[Generated Date] = '01/10/2008' AND 
                      jez.WL_DATA.[Waiting List Name] IS NULL AND jez.WL_DATA.[Date Removed from Waiting List] IS NULL
GROUP BY jez.WL_RANGES.[weeks range title], jez.WL_RANGES.[weeks range]
ORDER BY jez.WL_RANGES.[weeks range]

How can I do this??

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-10-01 : 05:46:14

[code]SELECT TOP 1 MAX(jez.WL_DATA.[Weeks Waiting]) AS Max
FROM jez.WL_RANGES LEFT OUTER JOIN
jez.WL_DATA ON jez.WL_DATA.[Weeks Range] = jez.WL_RANGES.[weeks range] AND jez.WL_DATA.[Waiting List Service] = 'PHYSIO' AND
jez.WL_DATA.Month = 'September' AND jez.WL_DATA.Year = '2008' AND jez.WL_DATA.[Generated Date] = '01/10/2008' AND
jez.WL_DATA.[Waiting List Name] IS NULL AND jez.WL_DATA.[Date Removed from Waiting List] IS NULL
GROUP BY jez.WL_RANGES.[weeks range title], jez.WL_RANGES.[weeks range]
ORDER BY jez.WL_RANGES.[weeks range] DESC
[/code]

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-01 : 05:58:45

quote:

Originally posted by madhivanan

SELECT     TOP 1 MAX(jez.WL_DATA.[Weeks Waiting]) AS Max
FROM         jez.WL_RANGES LEFT OUTER JOIN
                      jez.WL_DATA ON jez.WL_DATA.[Weeks Range] = jez.WL_RANGES.[weeks range] AND jez.WL_DATA.[Waiting List Service] = 'PHYSIO' AND 
                      jez.WL_DATA.Month = 'September' AND jez.WL_DATA.Year = '2008' AND jez.WL_DATA.[Generated Date] = '01/10/2008' AND 
                      jez.WL_DATA.[Waiting List Name] IS NULL AND jez.WL_DATA.[Date Removed from Waiting List] IS NULL
GROUP BY jez.WL_RANGES.[weeks range title], jez.WL_RANGES.[weeks range]
ORDER BY jez.WL_RANGES.[weeks range]Max DESC

Madhivanan

Failing to plan is Planning to fail

i think you should be ordering by max instead

JezLisle
Posting Yak Master

132 Posts

Posted - 2008-10-01 : 06:04:20

Excellent, thanks.

How can I alter this and get it to input a zero if the result is null?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-01 : 06:14:35

quote:
Originally posted by JezLisle

Excellent, thanks.

How can I alter this and get it to input a zero if the result is null?

SELECT     TOP 1 COALESCE(MAX(jez.WL_DATA.[Weeks Waiting]),0) AS Max
FROM         jez.WL_RANGES LEFT OUTER JOIN
                      jez.WL_DATA ON jez.WL_DATA.[Weeks Range] = jez.WL_RANGES.[weeks range] AND jez.WL_DATA.[Waiting List Service] = 'PHYSIO' AND 
                      jez.WL_DATA.Month = 'September' AND jez.WL_DATA.Year = '2008' AND jez.WL_DATA.[Generated Date] = '01/10/2008' AND 
                      jez.WL_DATA.[Waiting List Name] IS NULL AND jez.WL_DATA.[Date Removed from Waiting List] IS NULL
GROUP BY jez.WL_RANGES.[weeks range title], jez.WL_RANGES.[weeks range]
ORDER BY jez.WL_RANGES.[weeks range]Max DESC

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-10-01 : 08:47:26

quote:

Originally posted by visakh16

quote:

Originally posted by madhivanan

SELECT     TOP 1 MAX(jez.WL_DATA.[Weeks Waiting]) AS Max
FROM         jez.WL_RANGES LEFT OUTER JOIN
                      jez.WL_DATA ON jez.WL_DATA.[Weeks Range] = jez.WL_RANGES.[weeks range] AND jez.WL_DATA.[Waiting List Service] = 'PHYSIO' AND 
                      jez.WL_DATA.Month = 'September' AND jez.WL_DATA.Year = '2008' AND jez.WL_DATA.[Generated Date] = '01/10/2008' AND 
                      jez.WL_DATA.[Waiting List Name] IS NULL AND jez.WL_DATA.[Date Removed from Waiting List] IS NULL
GROUP BY jez.WL_RANGES.[weeks range title], jez.WL_RANGES.[weeks range]
ORDER BY jez.WL_RANGES.[weeks range]Max DESC

Madhivanan

Failing to plan is Planning to fail

i think you should be ordering by max instead

Well. Thanks

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources