MAX function - 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

MAX function

Author

Topic

jcb267
Constraint Violating Yak Guru

291 Posts

Posted - 2009-03-10 : 09:47:48

Can anyone help me to use the MAX function in SQL 2005? I am trying to take the maximum value in a date field:

hosp_rpt.fy_end_dt = max

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2009-03-10 : 10:10:54

Select Max(hosp_rpt.fy_end_dt)as M from TabelName
if you want Max for certain groups e.g. for CustomerID - use GROUP BY
e.g.
Select Max(hosp_rpt.fy_end_dt)as M from TabelName
GROUP BY CustomerID

jcb267
Constraint Violating Yak Guru

291 Posts

Posted - 2009-03-10 : 11:02:20

quote:
Originally posted by darkdusky

Select Max(hosp_rpt.fy_end_dt)as M from TabelName
if you want Max for certain groups e.g. for CustomerID - use GROUP BY
e.g.
Select Max(hosp_rpt.fy_end_dt)as M from TabelName
GROUP BY CustomerID

Thanks for the help. I tried this but it is not working. Is it because of the format of the date field?

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-03-10 : 11:04:46

Please post the query you tried....aslo give us sample data and expected output.

jcb267
Constraint Violating Yak Guru

291 Posts

Posted - 2009-03-10 : 12:56:39

quote:
Originally posted by vijayisonly

Please post the query you tried....aslo give us sample data and expected output.

select hosp_rpt.prvdr_num, hosp_nmrc.Itm_val_num, hosp_nmrc.wksht_cd, hosp_nmrc.line_num,
hosp_nmrc.Clmn_num,max(hosp_rpt.fy_end_dt)
from hosp_nmrc INNER JOIN hosp_rpt
ON hosp_nmrc.RPT_REC_NUM = hosp_rpt.RPT_REC_NUM
where (hosp_nmrc.wksht_cd like 'S300001%'and hosp_nmrc.line_num = '01200' and hosp_nmrc.Clmn_num = '0600')
and (hosp_rpt.prvdr_num = 490011 or hosp_rpt.prvdr_num = 490017 or
hosp_rpt.prvdr_num = 490069 or hosp_rpt.prvdr_num = 490094 or
hosp_rpt.prvdr_num = 490059 or hosp_rpt.prvdr_num = 490136 or
hosp_rpt.prvdr_num = 490041 or hosp_rpt.prvdr_num = 493301 or
hosp_rpt.prvdr_num = 490032 or hosp_rpt.prvdr_num = 490119 or
hosp_rpt.prvdr_num = 490093 or hosp_rpt.prvdr_num = 490046 or
hosp_rpt.prvdr_num = 490044 or hosp_rpt.prvdr_num = 490007 or
hosp_rpt.prvdr_num = 490057 or hosp_rpt.prvdr_num = 420004 or
hosp_rpt.prvdr_num = 490024)
Group By hosp_rpt.prvdr_num, hosp_nmrc.Itm_val_num, hosp_nmrc.wksht_cd, hosp_nmrc.line_num, hosp_nmrc.Clmn_num
order by hosp_rpt.prvdr_num

I think the query is working properly, just not giving me the results I want.
Here is a sample of what I would expect to see:
prvdr_num item_val_num wksht_cd line_num clmc_num fy_end_dt
420004 136249 S300001 1200 600 6/30/2001 0:00

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-10 : 12:58:31

see this to understand the format to post a question
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

jcb267
Constraint Violating Yak Guru

291 Posts

Posted - 2009-03-10 : 13:07:37

quote:
Originally posted by visakh16

see this to understand the format to post a question
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

That question is not phrased properly for you? What is not clear about it?

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-03-10 : 13:10:01

quote:
Originally posted by jcb267

quote:
Originally posted by visakh16

see this to understand the format to post a question
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

That question is not phrased properly for you? What is not clear about it?

Remember We can't read you mind nor want to. Post sample data and expected output for answer.

jcb267
Constraint Violating Yak Guru

291 Posts

Posted - 2009-03-10 : 13:12:23

quote:
Originally posted by sodeep

quote:
Originally posted by jcb267

quote:
Originally posted by visakh16

see this to understand the format to post a question
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

That question is not phrased properly for you? What is not clear about it?

Remember We can't read you mind nor want to. Post sample data and expected output for answer.

More than this?

Here is a sample of what I would expect to see:
prvdr_num item_val_num wksht_cd line_num clmc_num fy_end_dt
420004 136249 S300001 1200 600 6/30/2001 0:00

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-10 : 13:25:54

ok. what should be data out of which you want above result?

Also, Is it so difficult to read link and post question as per instructions in it?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources