using "Max" in queries - 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 2000 Forums

Transact-SQL (2000)

using "Max" in queries

Author

Topic

iamsmahd
Yak Posting Veteran

55 Posts

Posted - 2004-11-01 : 05:10:36

Hi every body.

I wonder that sometimes we use "MAX" in our queries in order to retrieve the Mazimum of some thing,and some times we use MAX in queries not for retrieving the Maximum of some thing but retrieving the exact number of some thing .....

I would like to know more about this.....

Thank you very much

sonia

surefooted
Posting Yak Master

188 Posts

Posted - 2004-11-01 : 10:04:32

Look up Max in books online. If you still have questions let us know.

-Jon
Should still be a "Starting Member"

dsdeming

479 Posts

Posted - 2004-11-02 : 10:19:44

How are you using MAX to retrieve the "exact number of some thing"? The COUNT function will get you that, but not MAX. Can you show us an example?

Dennis

iamsmahd
Yak Posting Veteran

55 Posts

Posted - 2004-11-04 : 05:17:36

yes....here it is:

(
Select Max(isnull(Subsid,0))
From se_ProductPrice
Where ProductCode = Se_PackageingMixture.MixProductCode AND PriceGroupCode = SeSellerReceptCloseReportVIEW.PriceGroupCode AND RundateSubsid =(
Select Max(Rundatesubsid)
From Se_ProductPrice
where ProductCode = Se_PackageingMixture.MixProductCode AND PriceGroupCode = SeSellerReceptCloseReportVIEW.PriceGroupCode AND Rundatesubsid<=SeSellerReceptCloseReportVIEW.RDate)
)As Subsid
)

This query returns the exact subsid not the maximum in a selected group

sonia

nr
SQLTeam MVY

12543 Posts

Posted - 2004-11-04 : 06:17:58

No it doesn't

Select *
From se_ProductPrice
Where ProductCode = Se_PackageingMixture.MixProductCode AND PriceGroupCode = SeSellerReceptCloseReportVIEW.PriceGroupCode AND RundateSubsid =(
Select Max(Rundatesubsid)
From Se_ProductPrice
where ProductCode = Se_PackageingMixture.MixProductCode AND PriceGroupCode = SeSellerReceptCloseReportVIEW.PriceGroupCode AND Rundatesubsid<=SeSellerReceptCloseReportVIEW.RDate)
)As Subsid

will return a resultset
the Select Max(isnull(Subsid,0))
will return the max value from that resultset.

The resultset will be the rows with the latest Rundatesubsid for each PriceGroupCode / ProductCode

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

iamsmahd
Yak Posting Veteran

55 Posts

Posted - 2004-11-06 : 04:20:25

nr,
thanks for your pay attention.

but here I do not want to get the Maximum of subsid, but the one which

is compatible with my condotions :
(ProductCode = Se_PackageingMixture.MixProductCode AND ....)
but I wonder that "Select MAX(isnull(subsid),0)" returns my wanted susid not the maximum one in he selected group...!

sonia

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources