Grab Max value - 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

Grab Max value

Author

Topic

Vack
Aged Yak Warrior

530 Posts

Posted - 2008-09-11 : 15:02:29

Have Two tables.
orderebatehistory
oehdrhst

linked fields:
ord_no
Ord_type

There could be multiple records in the oehdrhst table with the same ord_no and ord_type.

What I would like to do is grab the max invoice number from those records in the oehdrhst and update it to the orderrebatehistory table.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-09-11 : 15:04:01

You can use the MAX function. You'll also need to GROUP BY those columns.

Something like this:

SELECT Column1, Column2, MAX(Column3)
FROM Table1
GROUP BY Column1, Column2

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Vack
Aged Yak Warrior

530 Posts

Posted - 2008-09-11 : 15:05:47

I want to update orderrebatehistory with the max invoice number. Having trouble with the update statement. I don't have a problem doing a select.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-09-11 : 15:06:17

Show us a data example then.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Vack
Aged Yak Warrior

530 Posts

Posted - 2008-09-11 : 15:08:25

oehdrhst:

ord_no ord_type invoice#
22 o 123
22 o 321
22 o 555

Out of the records above I want to grab the max invoice (555) and update the orderrebatehistory inv_no field.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-09-11 : 15:12:25

UPDATE orh
SET inv_no = oh.inv_no
FROM orderrebatehistory orh
INNER JOIN (SELECT ord_no, ord_type, MAX(inv_no) AS inv_no FROM oehdrhst) oh
ON orh.ord_no = oh.ord_no AND orh.ord_type = oh.ord_type

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Vack
Aged Yak Warrior

530 Posts

Posted - 2008-09-11 : 15:21:54

Received the following when trying to run:

column oehdrhst.ord_no is invalid in the select list because it is not contained in either an aggregate fundtion or the group by clause.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-09-11 : 15:25:45

Sorry forgot the GROUP BY:

UPDATE orh
SET inv_no = oh.inv_no
FROM orderrebatehistory orh
INNER JOIN (SELECT ord_no, ord_type, MAX(inv_no) AS inv_no FROM oehdrhst GROUP BY ord_no, ord_type) oh
ON orh.ord_no = oh.ord_no AND orh.ord_type = oh.ord_type

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources